Filter op many-to-many
Ik heb een many-to-many met drie database tabellen:
1) recepts
-id
-name
2) ingredients
-id
-name
3) recepts-ingredients
-recept_id
-ingredient_id
Zo kan ik makkelijk alle ingrediënten laten zien die nodig zijn voor een recept.
Maar nu wil ik alleen de recepten laten zien waarin bijvoorbeeld de ingrediënten 'tomaten' en 'pesto' voorkomen. Hoe ziet deze query er dan uit?
p.s. ik wil enkel die recepten waar BEIDEN ingrediënten in zitten en niet die waar één van beide inzit.
Gewijzigd op 05/10/2015 13:04:34 door Frank Nietbelangrijk
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT
r.id
FROM
recepts r
JOIN recepts_ingredients ri
ON ri.recept_id = r.id
JOIN ingredients tomaat
ON tomaat.name = 'tomaat' AND tomaat.id = ri.ingredient_id
JOIN ingredients pesto
ON pesto.name = 'pesto' AND pesto.id = ri.ingredient_id
r.id
FROM
recepts r
JOIN recepts_ingredients ri
ON ri.recept_id = r.id
JOIN ingredients tomaat
ON tomaat.name = 'tomaat' AND tomaat.id = ri.ingredient_id
JOIN ingredients pesto
ON pesto.name = 'pesto' AND pesto.id = ri.ingredient_id
Gewijzigd op 05/10/2015 13:13:28 door Ben van Velzen
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT
r.id, r.name, a.name
FROM
recepts r
JOIN recepts_ingredients ri
ON ri.recept_id = r.id
JOIN ingredients a
ON a.name = 'tomaat' AND a.id = ri.ingredient_id
JOIN ingredients b
ON b.name = 'pesto' AND b.id = ri.ingredient_id
r.id, r.name, a.name
FROM
recepts r
JOIN recepts_ingredients ri
ON ri.recept_id = r.id
JOIN ingredients a
ON a.name = 'tomaat' AND a.id = ri.ingredient_id
JOIN ingredients b
ON b.name = 'pesto' AND b.id = ri.ingredient_id
Dan krijg ik een lege resultaatset.
Als ik de laatste JOIN weglaat dan krijg ik alle recepten met het ingredient tomaat.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT
r.id, r.name, a.name
FROM
recepts r
JOIN recepts_ingredients ri1
ON ri1.recept_id = r.id
JOIN recepts_ingredients ri2
ON ri2.recept_id = r.id
JOIN ingredients a
ON a.name = 'tomaat' AND a.id = ri1.ingredient_id
JOIN ingredients b
ON b.name = 'pesto' AND b.id = ri2.ingredient_id
r.id, r.name, a.name
FROM
recepts r
JOIN recepts_ingredients ri1
ON ri1.recept_id = r.id
JOIN recepts_ingredients ri2
ON ri2.recept_id = r.id
JOIN ingredients a
ON a.name = 'tomaat' AND a.id = ri1.ingredient_id
JOIN ingredients b
ON b.name = 'pesto' AND b.id = ri2.ingredient_id
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
r.id,
r.name
FROM
recepts r
JOIN
recepts_ingredients ri
ON r.id = ri.recept_id
JOIN
ingredients i
ON ri.ingredient_id = i.id
WHERE i.name IN ('tomaat', 'pesto')
GROUP BY r.id
HAVING COUNT(*) = 2
r.id,
r.name
FROM
recepts r
JOIN
recepts_ingredients ri
ON r.id = ri.recept_id
JOIN
ingredients i
ON ri.ingredient_id = i.id
WHERE i.name IN ('tomaat', 'pesto')
GROUP BY r.id
HAVING COUNT(*) = 2
PS Het is recipe in het Engels ;-)
Gewijzigd op 05/10/2015 14:48:48 door Ger van Steenderen
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
r.id, r.name, GROUP_CONCAT(DISTINCT(i.name)) as items
FROM
recepts r
LEFT JOIN
recepts_ingredients ri
ON
ri.recept_id = r.id
LEFT JOIN
ingredients i
ON
ri.ingredient_id = i.id
GROUP BY
r.id
HAVING
FIND_IN_SET('tomaat',items) AND FIND_IN_SET('pesto',items)
r.id, r.name, GROUP_CONCAT(DISTINCT(i.name)) as items
FROM
recepts r
LEFT JOIN
recepts_ingredients ri
ON
ri.recept_id = r.id
LEFT JOIN
ingredients i
ON
ri.ingredient_id = i.id
GROUP BY
r.id
HAVING
FIND_IN_SET('tomaat',items) AND FIND_IN_SET('pesto',items)
Mocht er nog een beter of simpeler antwoord zijn dan hou ik me aanbevolen.
Toevoeging op 05/10/2015 14:59:40:
@Ger: Die van jou is korter en doet wat ie moet doen :-) Merci!
>> PS Het is recipe in het Engels ;-)
Ja daar kwam ik ook achter.