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

Reply via email to