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/[email protected]
