All geek questions in one place

How to sort rows in MySQL by the number of matches in an associative table?

I apologize because I really did not know how to put my question right, so I will explain my problem:

I have 3 simple MySQL tables:

  • table recipes with id column and many others that are not important for this problem.
  • ingredients table with two columns: id , name
  • associative table recipes_ingredients with two columns id id_recipes and id_ingredients

Now I want the user to search for recipes for their ingredients. The user can specify one or more ingredients at a time. I know how to write SQL for the case when I need recipes that match all user-defined ingredients or recipes that match AT LEAST ONE from the ingredients.

But my question is, how do I get the recipes ordered so that the first one matches the ingredients that the user searched for the ingredients.

I thought about it all day, but I did not come up with any ideas. Thanks for any help or tips in advance.

EDIT: My condition for the SQL query is that I want all recipes that have AT LEAST one component in the recipes_ingredients table, but I want the matching matching recipes to be sorted by the number of matching search ingredients.

For example: A user searches for 3 ingredients. There are 10 recipes that contain at least one of these ingredients. But I want to sort them, so the first recipe will be one that possibly matches all 3 ingredients found. Then there will be recipes that match only two ingredients, and the last time there will be recipes with one suitable ingredient.

+4
source share
1 answer

verified:

 select r.id, count(i.id) as ing_cnt from recipes r left join recipes_ingredients ri on ri.id_recipes = r.id left join ingredients i on ri.id_ingredients = i.id group by r.id order by ing_cnt desc
+2
source

Source: https://habr.com/ru/post/1501649/

More articles:


All Articles