[Dbix-class] binding variables to CASE WHEN

[Dagfinn Ilmari Mannsåker]

Augustus Saunders <asaunders at solfo.com> writes:

> You can use literal SQL with bound parameters in most places. It's a
> reference to an array ref: \[SQL, bind1, bind2,...].

This shold work everywhere, and definitely works in select => [...].

> You can also put ? into your sql and pass a bind array explicitly in
> the attributes. It may take some experimentation to figure out what
> works, as there are bugs in the bind parameter handling.

Which bugs, specifically? Have you reported them?

> If all else fails, you can ask your database handle to quote your
> values to avoid SQL injection.
>
> Augustus
>
> On Apr 7, 2015, at 10:24 AM, Adam Witney <awitney at sgul.ac.uk> wrote:
>
>> Hi,
>> 
>> I am trying to generate some counts in order to draw some charts. The query I have is this
[…]
>> Which is generated from
>> 
>> $rs = $c->model('DB::Culture')->search( $search, {
>> 	select => [
>>  		{ to_char => "collection_date, 'Mon'", -as => 'month' },
>>  		{ extract => "year from collection_date", -as => 'year' },
>> 		{ count => "CASE WHEN pathogen.name = '".$name1."' THEN 1 ELSE NULL END" },
>> 		{ count => "CASE WHEN pathogen.name = '".$name2."' THEN 1 ELSE NULL END" }

This will do what you want safely, using bind parameters:

    { count => \["CASE WHEN pathogen.name = ? THEN 1 ELSE NULL END", $name1] },

>>  	],
>> 	join => { 'culture_pathogens' => 'pathogen'  },
>> 	as => [ 'month','year', 'A', 'B' ],
>> 	order_by => 'year, month',
>> 	group_by => 'year, month'
>> });

Also, it's usually preferable to use

   columns  => { $dbic_alias => $sql_expr, … }

instead of

   select => [$sql_expr, …],
   as => [ $dbic_alias, … ],

as that makes it more obvious which SQL expression belongs to which
DBIC-side column name.

-- 
"I use RMS as a guide in the same way that a boat captain would use
 a lighthouse.  It's good to know where it is, but you generally
 don't want to find yourself in the same spot." - Tollef Fog Heen