Zbigniew Lukasiak wrote: > On 3/15/07, Guillermo Roditi <[EMAIL PROTECTED]> wrote: >> i guess you would either inner self join for every tag to only select >> items >> that have all those tags or you could do an IN(@tags) and then group by >> product id using a count() and then in the having clause count = scalar >> @tags >> >> SELECT id, name, COUNT(*) as cnt FROM products INNER JOIN tags >> ON(products.id = tags.product_id) WHERE tags.tag IN( @tags ) GROUP BY >> products.id HAVING cnt = scalar @tags > > I would like to know how efficient that query is - my intuition is > that it would have to scan all the groups to count their members so > this would mean a full scan on the joined table which can be pretty > big.
And Count(*) sucks goats on InnoDB. When it comes to counts, I'll implement a real tags_stats table. > >> >> or >> >> SELECT id, name, COUNT(*) as cnt FROM products >> INNER JOIN tags as tags1 ON(products.id = tags.product_id) >> INNER JOIN tags as tags2 ON(products.id = tags.product_id) >> WHERE tags1.tag ="computer" AND tags2.tag ="widget" >> > > I have translated that into DBIC at > http://perlalchemy.blogspot.com/2006/10/tags-and-search-and-dbixclass.html > (using bookmarks instead of products): I remember that page....couldn't find it again :-) > > I had to build hash with search parameters with the proper key names > (tag, tag_2, tag_3 ...) and values from the @tags array : > > my $suffix = ''; > my $i = 1; > for my $tag (@tags){ > $sqlparams{'tag' . $suffix . '.tag'} = $tag; > $suffix = '_' . ++$i; > } > > And then the search: > > my $it = $schema->resultset('Bookmark')->search( > \%sqlparams, { > join => [ ('tag') x scalar(@tags), 'usr' ], > order_by => [EMAIL PROTECTED], > page => $page, > rows => $maxrows, > }, > ); > >> From what I understood Chris needs to get not the products, but rather > the other tags related to them - so eventually another additional join > to the tags table is inevitable. > > -- > Zbyszek Ideally, once I'm out of the warm stick goo phase, I'll just use one of the search things to just do tag:foo tag:bar on an index... -=Chris
signature.asc
Description: OpenPGP digital signature
_______________________________________________ List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class Wiki: http://dbix-class.shadowcatsystems.co.uk/ IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/ Searchable Archive: http://www.mail-archive.com/[email protected]/
