Re: [Dbix-class] Filtering with Many-to-Many relationship
On Tue, Sep 22, 2009 at 7:23 AM, Oleg Kostyuk cub.ua...@gmail.com wrote: I think, you just need to join twice with tags table, and this will give you what you want: And if you need products which have 3 or more tags? One join per tag? It is not ideal. -- 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
Re: [Dbix-class] Filtering with Many-to-Many relationship
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.wall...@gmail.com mailto:reis.wall...@gmail.com wrote: On Fri, Sep 18, 2009 at 3:06 PM, Shawn Marincas shawngmarin...@gmail.com mailto:shawngmarin...@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 ___ 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
[Dbix-class] Filtering with Many-to-Many relationship
Alright, it seemed to me that what I wanted to do was straightforward, but it doesn't seem like I can find any resources on how to do it... so maybe I'm just going about it the wrong way. 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'; $c-model('MyApp:Tag')-single({ tag_id = $tag1 })-products-search({ tags = { tag_id = $tag2 }}); OR $c-model('MyApp:Tag')-single({ tag_id = $tag1 })-products-search_related('product_tags', { tag_id = $tag2 }); 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: $c-model('MyApp:Product')-search_related('product_tags',{ tag_id = $tag1 })-search_related('product_tags', { tag_id = $tag2 }); I tried the same chaining using search and joins to link to the ProductTagsLink table, they combined the searches so that it was joining on the related table searching for tag_id = $tag1 AND tag_id = $tag2 so that it wasn't returning anything. I was looking in to creating a subquery for this, but my attempts have failed and the documentation mentions that subqueries are experimental. I'm thinking if I had an accessor to access the many_to_many resultset as an actual resultset rather than a list of row_objects I could probably get it working, but I didn't see any documentation on actually doing that. I managed to get the actual concept working using this code here, but would prefer a straight SQL/DBIX solution since this doesn't seem very efficient: my $tag1 = 'A'; my $tag2 = 'B'; my $tag_rs = $c-model('ReaneyDesignDB::Tag')-single({ tag_id = $tag }); my @products = $tag_rs-products; $c-stash-{products} = [grep { ($_-search_related('product_tags', { tag_id = $tag2 })-single) } @products]; Anyone have experience trying to accomplish what I'm doing here? Let me know if I'm missing something here. Thanks. - Shawn Marincas ___ 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
Re: [Dbix-class] Filtering with Many-to-Many relationship
On Fri, Sep 18, 2009 at 3:06 PM, Shawn Marincas shawngmarin...@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 SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk