[Dbix-class] Chained resultsets and WHERE operators

[Steve]

It appears that you are *not* chaining these resultsets...

If you first do...
# Narrow the search down if the start date is supplied.
$date_ranges = $rs->search({
     start_date => { '<=', $start_date },
     end_date   => { '>=', $start_date },
}) if $start_date;

then in order to 'chain' you must...

$date_ranges = $date_ranges->search({...

You overwrote your resultset when you...

# Narrow the search down if the end date is supplied.
$date_ranges = $rs->search({
     start_date => { '<=', $end_date },
     end_date   => { '>=', $end_date },
}) if $end_date;



On 11/9/2010 8:36 AM, Mike Raynham wrote:
> Hi,
>
> I am probably missing something obvious here, but have been unable to 
> find a clean solution to the following problem, and I'm hoping that 
> your expert knowledge will come to the rescue.
>
> 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 },
>          ],
>      ],
> });
>
> ###
>
> This works correctly, but I would like to be able to run the search 
> for open ended date ranges - that is, if only the start or only the 
> end date is supplied.  I have attempted to do this by chaining search 
> queries, as follows:
>
> ###
>
> # Create a search.
> my $date_ranges = $rs->search({});
>
> # Narrow the search down if the start date is supplied.
> $date_ranges = $rs->search({
>     start_date => { '<=', $start_date },
>     end_date   => { '>=', $start_date },
> }) if $start_date;
>
> # Narrow the search down if the end date is supplied.
> $date_ranges = $rs->search({
>     start_date => { '<=', $end_date },
>     end_date   => { '>=', $end_date },
> }) if $end_date;
>
> ###
>
> This works correctly when either the start or end date is supplied. 
> However, when both dates are supplied, the two WHERE conditions are 
> chained together with an AND:
>
> # SELECT * FROM my_table WHERE
> # (start_date <= $start_date AND end_date >= $start_date) AND
> # (start_date <= $end_date   AND end_date >= $end_date)
>
> Instead, I need them to be chained together with an OR:
>
> # SELECT * FROM my_table WHERE
> # (start_date <= $start_date AND end_date >= $start_date) OR
> # (start_date <= $end_date   AND 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.
>
>
>
> Regards,
>
> Mike
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: 
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
>