[Dbix-class] Chained resultsets and WHERE operators

[Peter Rabbitson]

Mike Raynham wrote:
> On 09/11/10 16:14, Peter Rabbitson wrote:
>> Mike Raynham wrote:
>>>
>>> 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
>>
>> It's DBIx::Class. DBIx is a namespace with hundreds of modules in it,
>> it is not fair to the respective authors to default DBIx to DBIx::Class.
>> If you want to abbreviate DBIC is common.
> 
> Please accept my apologies.  I greatly appreciate all the work that has 
> gone into DBIx::Class and all the other modules in CPAN.  I didn't mean 
> to cause offence and will use DBIC or DBIx::Class in future.
> 
>>
>>> 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).
>>
>> I don't see why not... ? Explain what do you perceive the holdup would
>> be.
> 
> It's not that I thought there would be a holdup, I just don't know how 
> to do it, or even if it is possible.  I'm fairly new to perl, and very 
> new to the DBIx::Class, so have a lot to learn.
> 
> Given:
> 
> $rs->search({
>     start_date => { '<=', $start_date }
> });
> 
> ..the resulting SQL is along the lines of:
> 
> SELECT * FROM my_table WHERE start_date <= $start_date
> 
> I didn't realised that it was possible to switch the variable and table 
> column around to give this:
> 
> SELECT * FROM my_table WHERE $start_date => start_date
> 
> As you said that you didn't see why it wasn't possible, I've had another 
> look at the DBIx::Class::Manual::FAQ under ".. search with an SQL 
> function on the left hand side?".  The "-nest" modifier may be the 
> answer, but I've not tried it yet.
> 

-nest...? what...?

I still don't understand how this is different from anything you've showed
us in the thread so far. You said you want to do:

WHERE
($start_date >= start_date AND $start_date <= end_date)
   OR
($end_date   >= start_date AND $end_date   <= end_date)

In SQLA it is assumed that the lhs is a column and the rhs is a bind value
(this is a *generalization*, there are all kinds of exceptions, but as a
generalization it will suffice)

So we rewrite this as:
(start_date <= $start_date AND end_date >= $start_date)
   OR
(start_date <= $end_date AND end_date >= $end_date)

So then it boils down to:
[
   { start_date => { '<=', $start_date }, end_date => { '>=', $start_date } },
   { start_date => { '<=', $end_date }, end_date => { '>=', $end_date } },
]

That's it. No -nest, no braindamage.

Cheers