Re: DB_DataObject, complicated "Select"

T

I've found a solution, but is only SQL based and a bit ugly: --------------------------------------------- $covers=new DataObjects_Rel_countries(); $covers->query("SELECT p1.id_cover FROM rel_countries AS p1 " ."INNER JOIN rel_countries p2 USING(id_cover) " ."INNER JOIN rel_countries p3 USING(id_cover) " ."WHERE p1.id_country=10 " ."AND p2.id_country=7 " ."AND p3.id_country=11 " ."AND p2.id_country > p1.id_country " ."AND p3.id_country > p1.id_country");

This could be done as below.. - with a few modifications to joinAdd -to pick up that coversA == covers (eg. are the same object), and the match should occur on the primary key.. $covers = new DataObjects_Rel_countries; $coversA = new DataObjects_Rel_countries; $coversB = new DataObjects_Rel_countries; $covers->selectAs(); $covers->joinAdd($coversA, 'INNER', 'p2'); $covers->selectAs($coversA,'p2_%s'); $covers->joinAdd($coversB, 'INNER', 'p3'); $covers->selectAs($coversB,'p2_%s'); + a few whereAs's The only snag I see - is that your original SQL statement is probably slightly clearer...!... the other way is to do a number of queries and use IN() a few times.. - select id_covers's with id_country = 10; select id_covers where id_country = 11 and id_cover IN(...result from first..) -select id_covers where id_country = 12 and id_cover IN(...result from second.) I think you may be suprised to find this is might be just as fast.. ;) Regards Alan

while($covers->fetch()) { echo("cover: ".$covers->id_cover."<br>\n"); }; } --------------------------------------------- In this example, I have retrived records covers that haves 3 distincts id_countries. But If I want to retrive records covers that haves 100 countries, I would need to make 100 JOINS. there is a easier way to do it ? Thank you! mac -- ...ThE PhP_KiD. -> -----Mensaje original----- -> De: ThE PhP_KiD [mailto:gregoriandres@yahoo.com.ar] -> Enviado el: Lunes, 02 de Junio de 2003 03:11 p.m. -> Para: PEAR general list -> Asunto: [PEAR] DB_DataObject, complicated "Select" -> -> -> Hi, DB_DataObjects Users...! -> -> I've a two tables related, called -> "covers", and "rel_covers_countries" -> -> Covers -> ----------------------- -> id cover -> ----------------------- -> 1 napoleon, 1870

-> 2     san martin, 1820
-> 3     garibaldi, 1420

-> ......... -> ----------------------- -> -> -> And... -> -> rel_cover_countries -> --------------------- -> id_cover id_country -> --------------------- -> 1 10 -> 1 14 -> 1 12 -> 2 10 -> 2 11 -> 3 8 -> 3 10 -> 3 9 -> 3 11 -> --------------------- -> -> -> In last table you have all countries which -> covers are related. -> -> -> I need to select covers, with next criterion: -> -> (example:) -> -> If I want to get covers which haves -> id_country: 10 and 11, then database must -> returns me the id_cover 2, and 3. -> -> How can reach this result with DataObjects ? -> (or with SQL). -> -> -> Help me, please !!! -> -> mac -> -- -> ...ThE PhP_KiD. -> -> -- -> PEAR General Mailing List (http://pear.php.net/) -> To unsubscribe, visit: http://www.php.net/unsub.php