On 3/24/06, Michael Lackhoff <[EMAIL PROTECTED]> wrote: > Say, I have two tables: vendors and products. Now I want a list of all the > vendors that have no products yet. This can be seen from the products table if > there are no records with the vendor_id of the vendor in question. Is this > possible with a 'normal' manager method or with a relationship
This should work: $vendors = Vendor::Manager->get_vendors( with_objects => [ 'products' ], query => [ 'products.vendor_id' => undef ]); producing a query like this: SELECT ... FROM vendors t1 LEFT OUTER JOIN products t2 ON (t1.id = t2.vendor_id) WHERE t2.vendor_id IS NULL That may not be the most efficient way to do this query, however. Throwing a hard-coded SQL sub-query into the WHERE clause might be faster: SELECT ... FROM vendors v WHERE NOT EXISTS (SELECT * FROM products p WHERE p.vendor_id = id) You'd use the "clauses" param to do that: $subselect = 'NOT EXISTS (SELECT * FROM products p WHERE p.vendor_id = id)'; $vendors = Vendor::Manager->get_vendors(clauses => [ $subselect ]); > And if I have to hand-code it with get_objects_from_sql -- can you > give me a hint, what would be a good (fast, portable) SQL to do this > kind of query (my SQL isn't that good). It all depends on your database and your data. Try the two approaches above and see which one is more efficient for you...in terms of both the db and the maintainability and understandability of the code. There are always trade-offs :) > And how can I make the resulting method a method of my "Vendors" > class (I don't use separate manager classes)? Both of the above approaches should be possible in a relationship method by using the manager_args and query_args relationship parameters. -John ------------------------------------------------------- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642 _______________________________________________ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object