mysql add weights on query parameters

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.