Re: [CDBI] Searching related tables

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

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
methods.



        -- 
        Bill Moseley
moseley@xxxx.xxx


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