On Tue, Nov 9, 2010 at 8:36 AM, Mike Raynham <catal...@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:
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 },
])
: (),
],
});
Ronald
_______________________________________________
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
