mysql在查询参数上添加权重

Ok, for this question I need idea how to make an algorithm for sorting results.

Let me explain a problem:

i have different dish recipes (more than 2000) consists of different ingredients.

having 3 tables:

dish
id | name

ingredient
id | name

dish_ingredient
id | id_dish | id_ingredient

Application i made let users select different ingredients they have and app shows them proper dish recipe sort by count of ingredients they have. Now I would like to have algorithm where users would select ingredients and add them some kind of "weight".

Example which works for now: if user select ingredients beef, carrots, onion, salt, pepper algorithm looks which recipes has these ingredients (not necessary all of them) and sort them by ingredients that user has. So first recipe from this sorting could have just salt and then maybe flour and eggs (recipe for pancakes) at the end of list there could be recipes with beef which are more convenient for user.

So my idea is that user could add some weights on his ingredients so search algorithm would give him more proper recipes. If you do not understand what i want, you could see application on www.mizicapogrnise.si (transalate it from Slovenian language to your language) to see how app works now.

Thanks for all your help.

The basic query to count the ingredients is:

select di.id_dish, count(*) as NumIngredients
from dish_ingredient di join
     ingredient i
     on i.id = di.id_ingredient
group by di.id_dish;

You can modify this for ingredients by introducing a case clause into the query:

select di.id_dish, count(*) as NumIngredients,
       sum(case when i.name = 'beef' then 2.0
                when i.name = 'salt' then 0.5
                . . .
           end) as SumWeights
from dish_ingredient di join
     ingredient i
     on i.id = di.id_ingredient
group by di.id_dish
order by SumWeights desc;

The . . . is not part of the SQL syntax. You have to fill in with similar lines where this occurs.

An alternative formulation is to put the information in a subquery. The subquery contains the weight for each ingredient. This is then joined to the dish_ingredient table to get the weight for each ingredient in each dish. The outer query aggregates the results by dish:

select di.id_dish, count(*) as NumIngredients,
       sum(w.weight) as SumWeights
from dish_ingredient di join
     ingredient i
     on i.id = di.id_ingredient left outer join
     (select 'beef' as name, 2.0 as weight union all
      select 'salt', 0.5 union all
      . . .
     ) w
     on i.name = w.name
group by di.id_dish
order by SumWeights desc;

Both these method require modifying the query to get the necessary weight information into the database.