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 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" On 3/15/07, Christopher H. Laco <[EMAIL PROTECTED]> wrote:
Zbigniew Lukasiak wrote: > First - do you want to have all the tags of all the products that have > *all* of the selected tags or you want to have all the tags of all the > products that have *any* of the selected tags? > > From the pseudocode it seems that you want the latter, but I am still > not sure. > > -- > Zbyszek All. Given: http://.../tags/foo/bar/ I want all tags assigned to the products that have foo AND bar assigned to them. So yeah, "in" was a misnomer. :-/ -=Chris _______________________________________________ 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]/
_______________________________________________ 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]/
