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

[Chris Cole]

On 17/02/11 15:17, Rob Kinyon wrote:
> On Thu, Feb 17, 2011 at 10:01, Chris Cole<chris at compbio.dundee.ac.uk>  wrote:
>> Hi all,
>>
>> I've got a table where two of the columns (say a and b) are simple integers
>> and I want to select rows where b - a<  100. How do I go about that in DBIx.
>>
>> The equivalent SQL would be:
>> SELECT * FROM table WHERE b - a<  100;
>
> You have a few possibilities:
>
> 1) The easiest solution is $rs->search{ \'b - a<  100' );
> 2) b - a<  100 is equivalent to b<  a + 100. So, that would be
> $rs->search({ b =>  { '<' =>  \'a + 100' } });
>
> But, in both cases, you might want to parameterize the 100, so:
> $rs->search(\[ 'b - a<  ?', { dummy =>  100 } ]);
> $rs->search({ b =>  { '<' =>  \[ 'a + ?', { dummy =>  100 } ] };

Thanks for the reply.

I got the simple solution working fine, but the parameterised ones 
didn't work first off. After a bit of fiddling I got this working:

$rs->search({ b =>  { '<' =>  \[ 'a + ?', [ dummy =>  100 ] ] };

Cheers.