[Dbix-class] Functions in complex WHERE clauses

Rob Kinyon rob.kinyon at gmail.com
Sun Mar 8 19:36:26 GMT 2009


On Sat, Mar 7, 2009 at 22:04, Trevor Phillips <trevor.phillips at gmail.com> wrote:
> I have a particular where condition I'm trying to use in a search,
> which I can write quite easily in SQL, but am struggling to find a
> "nice" way of doing using the DBIx syntax.
>
> Basically, I want to return all rows which a certain field is a
> partial string match to a reference string. For example, if my
> reference string is "/foo/bar/", then fields with values "", "/",
> "/foo/", "/foo/bar/" would match, but "/foo/bar/foo" would not.
>
> In SQL there's a couple of ways I could do this:
>
> select * from my_table where '/foo/bar/' like concat(my_field,'%');
> select * from my_table where my_field = substr('/foo/bar/',1,length(my_field));
>
> I'm not sure which is more efficient, but they both work. ^_^
>
> However, representing this in a DBIx::Class search condition has
> proven tricky. The best I can come up with is to use the second
> technique and do something like the following:
>
> $refstring = '/foo/bar/';
> $where = " = substr('".sqlescape($refstring)."',1,length(my_field))";
> $result = $fullset->search( { my_field => \$where } );

$rs->search([
    \[ "? LIKE CONCAT( my_field, '%' )", "/foo/bar/" ],
]);

Note the arrayref and not hashref as the first argument to search.

Note also that this will only work with the latest dev releases of
SQL::Abstract and DBIx::Class.

Rob



More information about the DBIx-Class mailing list