I am working with django-categories which uses django-mptt under the hood. (That implicates db structure that I am using.)
I have some products:
product
--------
id | name
1 | Apple
2 | Orange
3 | Tomato
Categories:
categories
----------
id | name | parent | tree_id | level
1 | Type | null | 1 | 1
2 | Fruit | 1 | 1 | 2
3 | Vegetable | 1 | 1 | 2
4 | Color | null | 2 | 1
5 | Orange | 4 | 2 | 2
6 | Red | 4 | 2 | 2
7 | Green | 4 | 2 | 2
8 | Dark green | 7 | 2 | 3
9 | Orange | 4 | 2 | 2
Which is tree with single root (nodes have also fields order, left, right but I don't think that it is relevant here):
root/
├── Type/
│ ├── Fruit
│ ├── Vegetable
└── Color/
├── Red
├── Green
| └── Dark green
└── Orange
And M2M table:
product_categories
------------------
id | product_id | category_id
1 | 1 | 2
2 | 1 | 6
3 | 2 | 2
4 | 2 | 9
5 | 3 | 3
6 | 3 | 5
So I am having all categories in one tree, but I can group them by tree_id.
Now I would like to find products that are Fruits and are Red OR Orange (so to get Apple an Orange)
I was expecting that query to give me what I want:
SELECT DISTINCT
"product"."id", "product"."name"
FROM
"product"
INNER JOIN "product_categories" ON ("product"."id" = "product_categories"."product_id")
WHERE
("product_categories"."category_id" IN ('2')
AND "product_categories"."category_id" IN ('6', '9'))
but I am getting no results.
It could be achieved by querying multiple times, once per tree, and then looking for intersection, but I believe it is doable by single query.
Try this:
SELECT DISTINCT
"product"."id", "product"."name"
FROM
"product"
INNER JOIN "product_categories" ON ("product"."id" = "product_categories"."product_id")
WHERE
("product_categories"."category_id" IN ('6', '9'))
INTERSECT
SELECT DISTINCT
"product"."id", "product"."name"
FROM
"product"
INNER JOIN "product_categories" ON ("product"."id" = "product_categories"."product_id")
WHERE
("product_categories"."category_id" IN ('2'))