How to implement LIKE functionality in a grouped query that depends on an IN operator? [closed]

After some great interaction here in Stack Overflow I am able to implement queries to get all dishes that include ingredients A and B and those that are composed of ONLY ingredients A and B.

SELECT d.id, d.name 
FROM dishes d
JOIN dishes_ingredients di ON d.id = di.dishes_id
JOIN ingredients i ON di.ingredients_id = i.id
GROUP BY d.id
HAVING COUNT(DISTINCT CASE WHEN i.name IN ('apple', 'pear') THEN i.name END) = 2
AND COUNT(DISTINCT i.name) = 2  //--- for exclusivity

Now, when users search for apple and pear, they should also find a dish called “fruit wonder” that contains Fuji apples and pear jam. Dishes containing ingredients such as green apple, red apple, apple jam, pear flavor, etc, should also be found. If I use the above query and search for apple and pear I will not find the “fruit wonder” dish.

Answer

I think you want:

HAVING SUM(i.name LIKE '%apple%') > 0 AND
       SUM(i.name LIKE '%pear%') > 0

You might also want:

SUM( i.name NOT LIKE '%apple%' AND i.name NOT LIKE '%pear%') = 0

If you want only apples and pears.