[jifty-devel] Using OR in a Collection->limit

[Wolfgang Kinkeldei]

Am 12.10.2006 um 16:44 schrieb Henry Baragar:

> Hello,
>
> We need to be able to select rows
>
> select * from Trade where tdate = '2006-10-11' and (buyer = 21 or  
> seller = 21)
>
> Here is our attempt using a Jifty collection:
>
>     $date = '2006-10-11';
>     $cust = 21;
>     my $trades= Billing::Model::TradeCollection->new;
>     $filled->limit(column => "tdate", value => $date);
>     $filled->limit(column => "buyer", value => $cust);
>     $filled->limit( entry_aggregator => 'or',
>        column => "seller", value => $cust);
>
> The "OR" never gets into the select clause:  The last two limit's  
> are "AND"ed.
>
> What are we doing wrong?  How do we fix this?

I recently had the very same problem. Unfortunately, the 'or'  
operator is the default for 'entry_aggregator' which at least led me  
to the conclusion that the conditions will be ORed which is not the  
case...

When applying multiple 'limit' calls you usually constuct a 'Clause'  
in the teminology of Jifty::DBI::Collection. All clauses are  
automatically ANDed together which usually is a good default. To use  
some other operator than 'AND', you would have to build a 'subclause'  
by adding the 'subclause' argument using the same key for all  
operators that you like to get connected by this operator (if the  
'subclause' name is the same).

If you write:

    $filled->limit(column => 'tdata', value => $data);
    $filled->limit(column => 'buyer', value => $cust,
                   subclause => 'buyer_seller',
                   entry_aggregator => 'or');
    $filled->limit(column => 'seller', value => $cust,
                   subclause => 'buyer_seller',
                   entry_aggregator => 'or');

you will get a SQL statement like that you wanted.

During experimenting, it might help to see the SQL that would be  
generated:
    warn $filled->build_select_query();


Regards,

Wolfgang Kinkeldei

-- 

' /\_/\ ' .print[split??,"".(($/=q|Cms)+-03467:;<=|)=~tr!C-z -B! -z!)x
'( o.o )' .$/]->[hex]foreach split qr<>,qq+1ecd039ad65b025b8063475b+||
' > ^ < ' .q<!-- Wolfgang Kinkeldei - mailto:wolfgang at kinkeldei.de -->