how to repersent N:M in DBIx?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

how to repersent N:M in DBIx?

Rajeev Prasad
Pl refer to this link, it has my exact problem (but it only tells about SQL solution): http://stackoverflow.com/questions/11409869/mysql-nm-relationship-find-rows-with-several-specific-relations

I have three tables:
1. products:-> prod-id, prod-name
2. tags:-> tag-id, tag-name
3. product_tags::-> prod-id, csv_tag_id

i read elsewhere on internet that this is not a good database/table design. so i am ready to change that too, but i could not think of any other way to represent this relationship. a given product could have multiple tags, but i have to find - at a given time - only those products which have 'ALL' of multiple given tags.

the SQL suggested on the page is:

SELECT a.*
FROM products a
INNER JOIN product_tags b ON a.product_id = b.product_id
WHERE b.tag_id IN (1,23,54)
GROUP BY a.product_id
HAVING COUNT(1) = 3
thank you.
Rajeev

_______________________________________________
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@...