[CDBI] Searching related tables

Bill Moseley moseley at hank.org
Mon Dec 19 15:12:57 GMT 2005

On Mon, Dec 19, 2005 at 01:41:05PM +0000, Will Hawes wrote:
> 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).

Take a look at the "sql_method" in the current Sweet.

For example, you could place this in your base class:

    __PACKAGE__->set_sql('No_Dups', <<'' );
        SELECT      __ESSENTIAL(me)__
        FROM        %s
        WHERE       %s
        GROUP BY    __ESSENTIAL(me)__
        %s %s

    __PACKAGE__->set_sql('No_Dups_Count', <<'' );
        SELECT      COUNT(*) from
            ( SELECT    me.id
              FROM      %s
              WHERE     %s
              GROUP BY  me.id
            ) as total_count

Then specify the sql_method => 'No_Dups' when calling one of the search

Bill Moseley
moseley at hank.org

More information about the ClassDBI mailing list