* Getting recipe steps *
* Getting Recipe Ratings *
LEFT JOIN recipe_steps ON recipe_steps.recipe_key = recipes.recipe_key;
* Getting Recipes, Ingredients, Units and data for conversions
SELECT COUNT(*) AS rating_amount, SUM(rating_score)
LEFT JOIN ratings ON ratings.recipe_key = recipes.recipe_key;
SELECT recipes.recipe_name,ingredients.ingred_name, recipe_ingredients.unit_quantity, units.unit_name,conversions.*,unti2.unit_name AS conv_unit_name
LEFT JOIN recipe_ingredients ON recipe_ingredients.recipe_key = recipes.recipe_key
LEFT JOIN ingredients ON ingredients.ingred_key = recipe_ingredients.ingred_key
LEFT JOIN units ON units.unit_key = recipe_ingredients.unit_key
LEFT JOIN conversions ON conversions.from_unit_key = units.unit_key
LEFT JOIN units unti2 ON unti2.unit_key = conversions.to_unit_key
I would have to loop through the result and calculate: $converted_unit = (unit_quantity / from_unit_quantity) * to_unit_quantity
$converted_unit would have the converted value.
Is there a way to reduce the amount of these 3 queries into 1 or 2 ?
I've tried different solutions, but I would always get duplicates because if the recipe has 2 images and 2 recipe steps it will return 4 lines with two duplicates.