Constructing a resultset()->search() with variable number of ORs?

[Chris Cole]

Hi,

I'd like to convert the below code to using a variable number of ORs on
the 'name' column. i.e. there may be a data3 as well as data1 and data2.

my $schema = DB::Schema->connect();

my @rows = $schema->resultset('Reads2expt')->search(
   # select name rows on data1 OR data2 with abundance cutoffs
   [
      {
         name => 'data1',
         abundance => { '>' => 1 }
      },
      {
         name => 'data2',
         abundance => { '>' => 1 }
      }
   
   ],
   {
      join => [qw/ read_id expt_id /],
      order_by => 'me.read_id',        
      group_by => 'me.read_id',        
      prefetch => 'read_id',          
      cache => 1
   }
);

I've gotten this far:
my @ors;
foreach my $data (qw/data1 data2/) {
   push @ors, { name => $data, abundance => { '>' => $abund } };
}
my @rows = $schema->resultset('Reads2expt')->search(
   @ors,
   {
      join => [qw/ read_id expt_id /],  
      order_by => 'me.read_id',        
      group_by => 'me.read_id',        
      prefetch => 'read_id',        
      cache => 1
   }
);

but it gives me very different results and the DBIC_TRACE output shows
that it's doing a series of subselects, not expanding the OR construct.

Can anyone see where I'm going wrong?
Cheers,

Chris


_______________________________________________
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@...

[Moritz Onken]

Am 26.10.2009 um 17:36 schrieb Chris Cole:

> Hi,
>
> I'd like to convert the below code to using a variable number of ORs  
> on
> the 'name' column. i.e. there may be a data3 as well as data1 and  
> data2.
>
> my $schema = DB::Schema->connect();
>
> my @rows = $schema->resultset('Reads2expt')->search(
>   # select name rows on data1 OR data2 with abundance cutoffs
>   [
>      {
>         name => 'data1',
>         abundance => { '>' => 1 }
>      },
>      {
>         name => 'data2',
>         abundance => { '>' => 1 }
>      }
>
>   ],
>   {
>      join => [qw/ read_id expt_id /],
>      order_by => 'me.read_id',
>      group_by => 'me.read_id',
>      prefetch => 'read_id',
>      cache => 1
>   }
> );
>
> I've gotten this far:
> my @ors;
> foreach my $data (qw/data1 data2/) {
>   push @ors, { name => $data, abundance => { '>' => $abund } };
> }
> my @rows = $schema->resultset('Reads2expt')->search(
>   @ors,
>   {
>      join => [qw/ read_id expt_id /],
>      order_by => 'me.read_id',
>      group_by => 'me.read_id',
>      prefetch => 'read_id',
>      cache => 1
>   }
> );

The first parameter needs to be an array ref:

> $schema->resultset('Reads2expt')->search(
>   \@ors,
>   {
>      join => [qw/ read_id expt_id /],
>      order_by => 'me.read_id',
>      group_by => 'me.read_id',
>      prefetch => 'read_id',
>      cache => 1
>   }
> );

_______________________________________________
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@...

[Chris Cole]

On Tue, 2009-10-27 at 13:40 +0100, Moritz Onken wrote:

> Am 26.10.2009 um 17:36 schrieb Chris Cole:
> >
> > I've gotten this far:
> > my @ors;
> > foreach my $data (qw/data1 data2/) {
> >   push @ors, { name => $data, abundance => { '>' => $abund } };
> > }
> > my @rows = $schema->resultset('Reads2expt')->search(
> >   @ors,
> >   {
> >      join => [qw/ read_id expt_id /],
> >      order_by => 'me.read_id',
> >      group_by => 'me.read_id',
> >      prefetch => 'read_id',
> >      cache => 1
> >   }
> > );
>
> The first parameter needs to be an array ref:
>
> > $schema->resultset('Reads2expt')->search(
> >   \@ors,
> >   {
> >      join => [qw/ read_id expt_id /],
> >      order_by => 'me.read_id',
> >      group_by => 'me.read_id',
> >      prefetch => 'read_id',
> >      cache => 1
> >   }
> > );

Ah, perfect. I knew it had to be something simple.
Thanks for your help.


_______________________________________________
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@...

[Peter Rabbitson-2]

Chris Cole wrote:

> Hi,
>
> I'd like to convert the below code to using a variable number of ORs on
> the 'name' column. i.e. there may be a data3 as well as data1 and data2.
>
> my $schema = DB::Schema->connect();
>
> my @rows = $schema->resultset('Reads2expt')->search(
>    # select name rows on data1 OR data2 with abundance cutoffs
>    [
>       {
>          name => 'data1',
>          abundance => { '>' => 1 }
>       },
>       {
>          name => 'data2',
>          abundance => { '>' => 1 }
>       }
>    
>    ],

...

o.O Why not simply:

search ({ abundance => { '>', 1 }, name => \@list_of_possible_names }, {...} );

_______________________________________________
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@...