[Dbix-class] Chained resultsets and WHERE operators

[Mike Raynham]

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