[Dbix-class] Re: using 'if' within where

Dagfinn Ilmari Mannsåker ilmari at ilmari.org
Tue Jul 3 13:19:29 GMT 2012


Sung Gong <sung at bio.cc> writes:

> Hi all,
>
> Is it possible to use 'if' function within a where clause?
> For example:
> where if (column_a is null, 1, column_a>=10)

This can be rewritten as 'column_a is null or column_a >= 10', e.g.:

   $rs->search({ column_a => [ undef, { '>=' => 10 } ] })

> I know the sql can be registered as a custom resultset, but my case of
> a bit complex (the number of binding values actually depends on the
> number of parameters received).
>
> SQL::Abstract does not explain the use of control flow functions.

Instead of creating a whole custom resultsource, can use literal
fragments with placeholders in ->search (possibly dynamically generating
the SQL and placeholder values):

   $rs->search(\["if(column_a is null, 1, column_a >= ?)",
                   [ column_a => 10 ]
              ])

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




More information about the DBIx-Class mailing list