On 09/11/10 15:02, Peter Rabbitson wrote:
Ronald J Kimball wrote:
On Tue, Nov 9, 2010 at 8:36 AM, Mike Raynham
<[email protected] <mailto:[email protected]>> 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)
Okay, thanks.
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 }, }
: ()
,
]);
Ronald, Peter: Thank you both for your input. I like the boiled-down
solution, and will go with that.
Also I think the <= >= signs are wrong there somewhere
Yes, it confused me for a while :-)
The query returns any rows where *either* the supplied start or end
dates are within the start and end date ranges held in 'my_table'. That
is, given a date range it checks the table to see if that date range
overlaps any of the rows in 'my_table'.
I have tested it, and it returns the results I would expect. I think
the confusion comes from the order in which the WHERE expression is
produced. If I were to write the SQL myself, without DBIx, I'd probably
swap around the column names and supplied dates, like this:
SELECT * FROM my_table WHERE
($start_date >= start_date AND $start_date <= end_date) OR
($end_date >= start_date AND $end_date <= end_date)
That way makes more sense to me, but I don't think I can write it like
that using DBIx (unless I opt for creating SQL directly).
As you will see, it does the same thing as the DBIx generated query -
it's just reversed:
SELECT * FROM my_table WHERE
(start_date <= $start_date AND end_date >= $start_date) OR
(start_date <= $end_date AND end_date >= $end_date)
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]
