[Dbix-class] Functions in complex WHERE clauses

[Trevor Phillips]

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 } );

Is there a nicer way of doing this query? Ideally, I'd prefer NOT to
have to manually SQL escape the reference string, and pass it in as a
parameter...

-- 
Trevor Phillips  - http://dortamur.livejournal.com/
"On nights such as this, evil deeds are done. And good deeds, of
course. But mostly evil, on the whole."
      -- (Terry Pratchett, Wyrd Sisters)