[Dbix-class] Filtering with Many-to-Many relationship

[Shawn Marincas]

It seems that the above query won't work in any case since searching for
tag_id IN a set will still returned the results OR'd together, and I need
the set of products which contain BOTH tags.  I've been toying with it some
more and came up with a solution which uses a subquery, still not sure if
this is the optimum solution yet or not.

$filtered_product_rs =3D $schema->resultset('Product')->search({
    'product_tags.tag_id' =3D> $tag2_rs->tag_id,
    'me.product_id' =3D> {
        -in =3D> [
            $schema->resultset('Product')->search_rs({
                'product_tags.tag_id' =3D> $tag_rs->tag_id
            },{
                join =3D> 'product_tags'
            })->get_column('product_id')->all
        ]
    }
}, {
    join=3D> 'product_tags'
});


On Fri, Sep 18, 2009 at 7:35 PM, Wallace Reis <reis.wallace at gmail.com>wrote:

> On Fri, Sep 18, 2009 at 3:06 PM, Shawn Marincas
> <shawngmarincas at gmail.com> wrote:
> > I have a table Products and a table Tags with a many_to_many relationsh=
ip
> > setup, the purpose of which would be to easily generate a resultset of
> > products with the given tag(s).  However, I'm running in to problems wh=
en
> I
> > want to generate a resultset of product records filtered with multiple
> > tags.  I was thinking that one of the following would work:
> >
> > my $tag1 =3D 'A';
> > my $tag2 =3D 'B';
>
> my $product_rs =3D $schema->resultset('Product')->search_rs({
>    'tag.tag_id' =3D> { -in =3D> [qw/A B/] }
> }, {
>    join =3D> { 'product_tags' =3D> 'tag' }
> });
>
> > But apparently the many_to_many relationship accessor only returns a li=
st
> of
> > row_objects rather than a resultset itself, so I can't perform resultset
> > searches on that.  So I tried the following to chain together two
> searches:
>
> You've used the ->search method in your code in list context, so it
> calls ->all over the resultset and returns a list of row objects.
>
> --
>     wallace reis/wreis         Catalyst and DBIx::Class consultancy with a
> clue
>     Software Engineer          and a commit bit:
> http://shadowcat.co.uk/catalyst/
>  Shadowcat Systems Limited
>  http://www.shadowcat.co.uk     http://www.linkedin.com/in/wallacereis
>
> _______________________________________________
> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090919/fd3=
22414/attachment.htm