[Dbix-class] Chained resultsets and WHERE operators

[Peter Rabbitson]

Ronald J Kimball wrote:
> On Tue, Nov 9, 2010 at 8:36 AM, Mike Raynham <catalyst at mikeraynham.co.uk 
> <mailto:catalyst at mikeraynham.co.uk>> wrote:
> 
> 
>     I have created a query which determines if a given date range exists
>     within date ranges stored in the database:
> 
>     ###
> 
>     # SELECT * FROM my_table WHERE
>     # (start_date <= $start_date AND end_date >= $start_date) OR
>     # (start_date <= $end_date   AND end_date >= $end_date)
> 
>     my $date_ranges = $rs->search({
>         -or => [
>             -and => [
>                 start_date => { '<=', $start_date },
>                 end_date   => { '>=', $start_date },
>             ],
>             -and => [
>                 start_date => { '<=', $end_date },
>                 end_date   => { '>=', $end_date },
>             ],
>         ],
>     });
> 
>  
> 
>     Is there a way that I can cause the two queries to be chained
>     together with an OR instead of an AND?
> 
>     At the moment, I have got round the problem by using the non-chained
>     query at the top of this post, and specifying out-of-range defaults
>     for dates that have not been supplied.
> 
> 
> I don't think there is a way to merge search conditions with an OR when 
> chaining resultsets.  I would suggest doing something like this:

This is correct. Chaining implies AND ing (tightening the condition more
and more)


> my $date_ranges = $rs->search({
>     -or => [
>         $start_date
>           ? (-and => [
>                 start_date => { '<=', $start_date },
>                 end_date   => { '>=', $start_date },
>              ])
>           : (),
>         $end_date
>           ? (-and => [
>                start_date => { '<=', $end_date },
>                end_date   => { '>=', $end_date },
>              ])
>           : (),
>     ],
> });
> 

The first argument to search() is any valid SQLA structure, thus the above
can be as simple as:

$rs->search([
   $start_date
     ? { start_date => { '<=', $start_date }, end_date   => { '>=', $start_date } }
     : ()
   ,
   $end_date =>
     ? { start_date => { '<=', $end_date }, end_date   => { '>=', $end_date }, }
     : ()
   ,
]);

Also I think the <= >= signs are wrong there somewhere