[Dbix-class] binding variables to CASE WHEN

[Augustus Saunders]

On Apr 8, 2015, at 5:03 AM, Dagfinn Ilmari Mannsåker <ilmari at ilmari.org> wrote:

> As explained in the Cookbook, you can pass the \[] directly to
> ->search(), or use { -and => [\[...], ...] } if you wish to combine it
> with other conditions:
> 
> https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Using-SQL-functions-on-the-left-hand-side-of-a-comparison

I would recommend that you don't refer people to that link, as that's generally speaking not how you want to accomplish that particular task.

> When I said "doesn't work" I wasn't talking about situations wher perl
> doesn't let you put a reference (e.g. hash keys), but places where DBIC
> accepted it, but did the wrong thing.

Let's say then that \[] is insufficient for all your variable binding needs due to Perl limitations.

> Could you please provide a minimal example where DBIC gets this wrong,
> without waiting to come up with a patch?  A quick experiment with bind
> parameters in all of GROUP_BY, HAVING and ORDER BY gets the order right
> for me using DBIC version 0.082820.
> 
> Or are you talking about using \'' with placeholders and sticking the
> parameters in the 'bind' attribute yourself?

So count gets confused if you use bind => [] with your select clauses; it drops the select clauses but leaves the bind parameters in. Outside of count, here is an example that messes up:

          'attrs' => {
                       'select' => [
                                     \[
                                         'aggfunction(col, ?)',
                                         'somevar'
                                       ]
                                   ],
                       'group_by' => 'somecolumn1',
                       'having' => {
                                     '-and' => [
                                                 {
                                                   'aggfunction(col, ?)' => {'>' => '0'}
                                                 }
                                               ]
                                   }
                       'bind' => [
                                   'somevar'
                                 ],
                     },
          'where' => {
                       '-and' => [
                                   {
                                     'somecolumn2' => 1
                                   }
                                 ]
                     }

The 'somevar' that comes from bind => ['somevar'] is not being correctly lined up with the unbound ? from the having clause. At the DBI level we bind_param(1,somevar), bind_param(2, somevar), bind_param(3, 1), bind_param(4, 0), but the order should go somevar, 1, somevar, 0.

Thanks-
Augustus