[Dbix-class] bind parameter syntax for an array

[Ben Tilly]

The number of bind parameters in the query has to match the number of
parameters passed in.  If you want to follow this approach you'll need
to either dynamically create the query on the fly to match the
request, or else pre-create versions with different numbers of bind
parameters and use the correct one.

Also you should be aware that large numbers of IN parameters in a
query of this form may perform poorly on the database side.

On Wed, Mar 28, 2012 at 8:40 AM, William Piel <william.piel at yale.edu> wrote:
> Hi all,
>
> I'd like to create a custom ResultSource query that uses "IN" in the SQL.
> How can I use the bind parameters so that I can pass an array to provide the
> contents for IN(…) ?   The below example works so long as @my_array has only
> one element -- beyond that I get a mismatch in the number of placeholders.
> Passing it \@my_array does not work.
>
> thanks,
>
> Bill
>
>
> package My::Schema::Result::TableT;
> use strict;
> use warnings;
> use base qw/DBIx::Class::Core/;
>
> __PACKAGE__->table_class('DBIx::Class::ResultSet::View');
>
> __PACKAGE__->result_source_instance->is_virtual(1);
>
> __PACKAGE__->result_source_instance->view_definition(q[
>    SELECT t.x, t.y, t.z
>    FROM table t
>    WHERE t.a = ?
>    AND t.b IN (?);
> ]);
>
> my $results = $schema->resultset( 'TableT' )->search( {}, { bind
>  => [ $my_scalar, @my_array ]} );
>
>
>
>
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk