[Dbix-class] Filtering a SELECT based on a calculation of two columns?

[Chris Cole]

On 21/02/11 16:36, Rob Kinyon wrote:
> On Mon, Feb 21, 2011 at 11:22, Chris Cole<chris at compbio.dundee.ac.uk>  wrote:
>> On 17/02/11 15:19, Fahad Khan wrote:
>>>
>>> This might help:
>>>
>>>
>>> http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Using_SQL_functions_on_the_left_hand_side_of_a_comparison
>>>
>>> http://search.cpan.org/~frew/SQL-Abstract-1.72/lib/SQL/Abstract.pm#Literal_SQL_with_placeholders_and_bind_values_%28subqueries%29
>>>
>>
>> Thanks. I did find those pages before, but couldn't understand them :( With
>> Rob Kinyon's reply I understand a bit more, but not much.
>
> What don't you understand? I would love to explain it so that you
> understand it, then you can reword it better. I don't know what I
> don't know I know. :)

Well, for a start I don't understand the syntax. Why do you only escape 
the first '[' and not the last? Knowing when to have a hashref or a 
arrayref is a bit of nightmare in DBIx. Most times, I plump for one and 
if it doesn't work I go with the other.

In the Cookbook example it's not clear where YEAR() and 'date_of_birth' 
come from. I very rarely work with dates and am unfamiliar with date 
manipulation functions. It would also be clearer to state that the 
'plain_value' assignment is there to replace the placeholder. For example:

"...the below will search for when the year of birth is 1979:
$rs->search(\[ 'YEAR(date_of_birth) = 1979' ]);

But, if you wish to use a variable in the comparison a different 
construct is required because there is no variable expansion within 
single quotes in perl:
$year = 1979;
$rs->search(\[ 'YEAR(date_of_birth) = ?', [ plain_value => $year ] ]);"

The second example in the cookbook, seems wrong. There are placeholders 
for two variables ('name' and 'YEAR(date_of_birth)') in the example SQL, 
but the DBIx code already has 'Bob' specified as the 'name'.

As per my original request, an example showing something other than 
equals would help in understanding how to deal with greater than or less 
than tests. Even now, I wouldn't know where to put a '<' in this 
complicated construct, without an example.

The SQL::Abstract documentation is worse. I've no idea what 'bindtype' 
'I' have set, seeing as /I/ am not using SQL::Abstract, but DBIx. I 
appreciate it's a more generic module than DBIx, but it's confusing to 
be directed to sub-SELECT documentation with no example SQL.