CODEWITHSUNDEEP
cakephp-2.0has-many
MadeOfSport
MadeOfSport

Reputation: 513

Find in hasMany where all Conditions are true

I have this tables:

products -> hasMany -> categories

Table: products

+----+--------+
| id | title  |
+----+--------+
|  1 | Prod 1 |
|  2 | Prod 2 |
|  3 | Prod 3 |
+----+--------+

Table: categories

+----+-------+------------+
| id | title | product_id |
+----+-------+------------+
|  1 | Cat 1 |          1 |
|  2 | Cat 2 |          1 |
|  3 | Cat 3 |          2 |
|  4 | Cat 1 |          1 |
+----+-------+------------+

How can i query the products which are in both categories "Cat 1" AND "Cat 2" in my example i want only find "Prod 1"

Upvotes: 0

Views: 65

Answers (1)

Jens Stigaard
Jens Stigaard

Reputation: 191

My intuition is that you should change the database, so you instead have a habtm relation.

So your tables should looks like this:

Products (id, title)
ProductCategories (product_id, category_id)
Categories (id, title)

So the product can belong to many categories.

Then you can look up your table for products belonging to category with ID = 1 and category with ID = 3, by doing following query:

MySQL query

SELECT Products.id, Products.title 
FROM Products
WHERE 
Products.id IN 
(
   SELECT c1.product_id 
   FROM ProductCategories AS c1 
     INNER JOIN ProductCategories AS c2
     ON c1.product_id = c2.product_id
   WHERE c1.category_id = 1 AND c2.category_id = 3
);

Upvotes: 1

Related Questions