[Dbix-class] Chained resultsets and WHERE operators

[Mike Raynham]

On 09/11/10 15:02, Peter Rabbitson wrote:
> Ronald J Kimball wrote:
>> On Tue, Nov 9, 2010 at 8:36 AM, Mike Raynham
>> <catalyst at mikeraynham.co.uk <mailto:catalyst at 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:
>
> 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