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=lnkkid0944bid$1720dat1642
___
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
