[Dbix-class] binding variables to CASE WHEN

[Dagfinn Ilmari Mannsåker]

Augustus Saunders <asaunders at solfo.com> writes:

> On Apr 7, 2015, at 11:00 AM, Dagfinn Ilmari Mannsåker <ilmari at ilmari.org> wrote:
>
>> 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 => [...].
>
> Alas it does not work with key names, since they must be strings. If
> you need to bind parameters on the left hand side of the operator, you
> have to use ? or do the quoting manually.

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

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.

>>> 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?
>
> Sorry, we haven't reported them yet. We're trying to prepare a patch
> for some other bugs, and I'm not sure if this was going to be covered
> or not. We are planning to fix the aliasing problems with count, but
> I'm not sure if we're fixing the bind problems with count. We also had
> bind problems with HAVING (separate from the counting bugs) but we're
> not sure if the problem is really with HAVING or mixing \[] and ?; it
> can't figure out what order to pass the bind parameters to DBI.

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?

> At any rate, in our experience when you start doing more complicated
> things like the OP is doing, a little trial and error is required, and
> ultimately we had to manually quote some things.

The things OP is doing with \[] is hardly complicated, and I'd be
_very_ surprised if anything went wrong there.

-- 
"A disappointingly low fraction of the human race is,
 at any given time, on fire." - Stig Sandbeck Mathisen