[Dbix-class] Query using function() on column name

[Bill Moseley]

On Tue, May 28, 2013 at 5:07 AM, Peter Rabbitson <rabbit+dbic at rabbit.us>wro=
te:

> On Mon, May 27, 2013 at 04:15:08PM -0700, Bill Moseley wrote:
> > I have created a partial index in Postgresql using lower() on a column
> name
> > so I need to use that in my query to make use of the index.
> >
> > The Cookbook<
> http://search.cpan.org/~ribasushi/DBIx-Class-0.08250/lib/DBIx/Class/Manua=
l/Cookbook.pod#Using_SQL_functions_on_the_left_hand_side_of_a_comparison
> >discusses
> > this usage.    But, I also need to identify the bind parameter.
> >
> > Is this the correct syntax for the bind value?
> >
> > my $rs =3D $schema->resultset( 'User' )->search(
> >     \['lower( first_name ) like ?', [{ dbic_colname =3D> 'first_name' },
> > "$query%"]],
> > );
>
> No it isn't. You have an extra set of []s in there. The "litral with
> bind" syntax is defined thus:
>
> \[ $sql_portion, $bind1, $bind2, $bind3,... ]
>

Ok, I'm confused by the docs, I guess.  They say:

Each bind value should be composed of an arrayref of [ \%args =3D> $val ].
> The format of \%args is currently:


So, my arrayref is:

[{ dbic_colname =3D> 'first_name' }, "$query%"]],


Oh, maybe it should just be this short-cut version?

\['lower( first_name ) like ?', [ first_name =3D> "$query%"]],


They both seem to generate this WHERE:

WHERE ( lower( first_name ) like ? ): 'foo%'


>
> > Second, what is the correct way to escape $query?
>
> There currently is no canned solution for this. The feature is blocked
> due to the unavailability of a way to mark some nodes as a specific
> dialect of SQL (per engine). In other words this is something which will
> take place post-Data::Query.
>

Ok, then I'll just try and filter the query on input.


Thanks,


-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130528/9d7=
b824a/attachment.htm