[Dbix-class] Chained resultsets and WHERE operators

[Mike Raynham]

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.