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

[Matt Whipple]

Shawn Marincas wrote:
> 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.
If you're worried about optimization, the one thing I would watch for is 
to ensure that the DBMS is still able to optimize the subquery by 
determining the most restrictive initial set of data, or otherwise 
implementing that optimization manually.  With something like tags I 
would assume that some are far more general than others so this could 
have a significant impact.  This is something that would normally occur 
with a self-join, but a subquery is likely a more fitting solution in 
your case. 

I may also suggest coupling this with a digest form of the tags (similar 
to a SET data type) which can then be run through a bitwise AND.  This 
would require organizing the tags (possibly into word-sized categories) 
but should allow for very quick checks that wouldn't add overhead as 
searches grew more complex.
>
> $filtered_product_rs = $schema->resultset('Product')->search({
>     'product_tags.tag_id' => $tag2_rs->tag_id,
>     'me.product_id' => {
>         -in => [
>             $schema->resultset('Product')->search_rs({
>                 'product_tags.tag_id' => $tag_rs->tag_id
>             },{
>                 join => 'product_tags'
>             })->get_column('product_id')->all
>         ]
>     }
> }, {
>     join=> 'product_tags'
> });
>
>
> On Fri, Sep 18, 2009 at 7:35 PM, Wallace Reis <reis.wallace at gmail.com 
> <mailto:reis.wallace at gmail.com>> wrote:
>
>     On Fri, Sep 18, 2009 at 3:06 PM, Shawn Marincas
>     <shawngmarincas at gmail.com <mailto:shawngmarincas at gmail.com>> wrote:
>     > I have a table Products and a table Tags with a many_to_many
>     relationship
>     > 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 when 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 = 'A';
>     > my $tag2 = 'B';
>
>     my $product_rs = $schema->resultset('Product')->search_rs({
>        'tag.tag_id' => { -in => [qw/A B/] }
>     }, {
>        join => { 'product_tags' => 'tag' }
>     });
>
>     > But apparently the many_to_many relationship accessor only
>     returns a list 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 <http://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
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> 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