[CDBI] Searching related tables

[prev] [thread] [next] [Date index for 2005/12/19]

This question actually relates to Class::DBI::Sweet. I'm assuming a few 
of you out there use it, apologies for going OT if that is not the case.

Using the following classes:

package My::Order;
__PACKAGE__->has_many('lines' => 'My::Orderline');

I would like to search for Orders based on column values of related 
classes. Class::DBI::Sweet allows me to do:

($users) = My::Order->search( { 'lines.product_id' => 1 } );

But I only want each Order to appear once in the objects returned. 
Class::DBI::Sweet does joins to allow searching of related tables, so it 
is possible for an Order to appear multiple times in the results 
returned (e.g. in the example above, if that Order has more than one 
Orderline with the same product_id).

So I need to do one of the following:

SELECT * FROM order
WHERE id IN
(SELECT order FROM orderline WHERE product_id=1)

or

SELECT * FROM order
JOIN orderline ON order.id = orderline.order
WHERE orderline.product_id = 1
GROUP BY order.id

It's not exactly a complex requirement so I'd like to avoid writing a 
custom SQL statement for every class that needs this functionality.

Is there some existing code out there that tackles this?

_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi