MySQL Forums
Forum List  »  Newbie

Can this be done in MySQL functions or is PHP best?
Posted by: Bodi K
Date: February 24, 2015 04:34PM

Hi,

I'm developing a recipe app and am trying to write a really efficient query, but it's a bit beyond me. All the appropriate indexes, etc are in place so I'll just give you a quick table layout so you get some background...

`recipe`
ID
Name

`ingredient`
ID
Name

`recipe-ingredient`
RecipeID
IngredientID
Quantity
UnitID

So far so good!


The idea here is recipes can have sub recipes. Think of nachos with salsa. Nachos has its own set of ingredients (chips, cheese, etc) but you could also serve your own custom salsa, which also has its own set of ingredients. It gets a bit complicated if there are sub-sub-recipes. Maybe that kick ass salsa recipe you've created uses some sort of hot sauce recipe that you've also created. So I have the following many-many table.

`recipe-recipe`
ParentID
ChildID
Quantity
UnitID


Continuing on...

`list_unit`
ID
Unit (grams, cups, litres, etc)
InGrams (cups, for example, would have 236)


Now this is where the real stuff I'm after comes into play. I want to be able to get the number of calories, protein and fat. I have the following table that has all that information for every ingredient

`nutrient`
ID
Name (Calories, protein, etc)

`nutrient_data`
IngredientID
NutrientID
Value (nutrient is measured assuming 100 grams of the ingredient. This is why UnitID is so important)


OK, now that all of that is out of the way -- I hope I haven't lost you lol -- I can get to the code I've written so far.

I'm thinking some sort of loop in PHP, but I feel that will do a lot of SQL calls and maybe there's a way to make one really awesome SQL statement!


function getIngredients($RecipeID) {
$ingredients = array();

$this->db->query("SELECT IngredientID, Quantity, UnitID FROM `recipe-ingredient` WHERE RecipeID=$RecipeID");
while ( $row = $this->db->nextRecord() )
array_push($ingredients, $row);

return $ingredients
}

//Get sub recipe ingredients
$this->db->query("SELECT ChildID FROM `recipe-recipe` WHERE ParentID=$RecipeID");
while ( $row = $this->db->nextRecord() )
array_push($ingredients, getIngredients($row['ChildID'])



I'm spinning my head trying to figure out how to get the 3rd level of sub recipes and beyond. It's unlikely there will ever be that many, but if it can exist i tneeds to be accounted for.

After I have all of the ingredients, I need to sum them all up and group them (it's possible there will be duplic ingredients, so this step is necessary)

$ingredients = somePHPFunctionToSumAndGroup($ingredients);

After I have the final list of ingredients, I need to get the nutrient information for each one. This step is large because the nutrient_data table is quite large (650,000 rows...as you can imagine, every ingredient X every nutrient). If I loop through each of the ingredients (say there are 10 in total) that means I'll be calling this massive table 10 times. A total waste.

Thoughts?

Options: ReplyQuote


Subject
Written By
Posted
Can this be done in MySQL functions or is PHP best?
February 24, 2015 04:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.