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

[Peter Rabbitson]

On Tue, May 28, 2013 at 07:41:56AM -0700, Bill Moseley wrote:
> On Tue, May 28, 2013 at 5:07 AM, Peter Rabbitson <rabbit+dbic at rabbit.us>wrote:
> 
> > 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/Manual/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 = $schema->resultset( 'User' )->search(
> > >     \['lower( first_name ) like ?', [{ dbic_colname => '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 => $val ].
> > The format of \%args is currently:
> 
> 
> So, my arrayref is:
> 
> [{ dbic_colname => 'first_name' }, "$query%"]],

You are correct, I misread what you had originally. Yes - everything is 
specified properly.

> Oh, maybe it should just be this short-cut version?
> 
> \['lower( first_name ) like ?', [ first_name => "$query%"]],
>

This is also valid, and is equivalent to the above

In reality all you want in this case is:

\['lower( first_name ) like ?', "$query%" ]

as there is no value for DBIC to be able to infer typo info for the 
specific bindval.

So in short - yes what you are doing is absolutely correct. Excuse my
being stupid.

Cheers