[Resolved] many to many search without redundant data

[bubblenut]

Hi,

I have three tables; recipe, recipe_ingredient, ingredient as described below. Underlined is the primary key, italic are foreign keys and bold is the table name.

[b]recipe[/b]
[u]recipe_id[/u]
name
desciption

[b]ingredient[/b]
[u]ingredient_id[/u]
name
description

[b]recipe_ingredient[/b]
[u]unique_id[/u]
[i]recipe_id[/i]
[i]ingredient_id[/i]

The poblem is with my search function and retrieving recipe data. If I do a query like the following

SELECT recipe.name as recipe_name, 
  recipe.description as recipe_description,
  ingredient.name as ingredient_name,
  ingredient.description as ingredient_description
FROM recipe, ingredient, recipe_ingredient
WHERE recipe.recipe_id=recipe_ingredient.recipe_id
&& ingredient.ingredient_id=recipe_ingredient.ingredient_id
&& (recipe.description LIKE '%something%' || ingredient.description LIKE '%something%'

I will get the results I'm looking for but I'll also get a lot of redundant data. Let's say on average each recipe has 10 ingredients that means I'm getting recipe_name and recipe_description an average of 10 times per recipe when I only need that data once, on large resultsets (say a recipe search on flour or oil :queasy: ) I'll be getting huge amounts of redundant data returned. Is this just an unavoidable downside of the relational model or is there a way around it.

Thanks
Bubble

[edit]
:0 never been a strong point
[/edit]

[nemonoman]

First off, I can't stand misspellng recipe for a whole answer. Sorry:

Your query might be returning a cartesian product BUT I think you are returning some recipe rows AND some ingredient rows with your double table OR query, and resolving ALL those returned rows which gives the impression of a cartesian product but isn't, exactly.

WHERE (recipe.description like 'cake' OR ingredient.name LIKE 'vanilla')

Will return both cake rows from recipe table
and vanilla rows from ingredient table

If you have 2 cake recipes with vanilla
And 2 vanilla ingredients
You'll have 4 rows that you are linking up.

What you MEAN to say, I think is:

SELECT recipe., ingredient.
FROM recipe, ingredient, recipe_ingredient
WHERE recipe_ingredient.recipe_id=recipe.id
AND recipe_ingredient.ingredient_id=ingredient.id
AND ingredient.name LIKE 'vanilla'
AND recipe.description LIKE 'cake'

Should do the trick.

Your uniqueid column, for recipe_ingredient is redundant, but not wrong.

[bubblenut]

Ah, no. My example was a little poor I think. Putting the OR in there just confused matters. The issue I'm having is that each recipie has many ingredients so if the query pulls two recipies each with 3 ingredients the resultset would look something like this (i've stripped out recipe and ingredient description for convenience)

recipe_name | ingredient_name
=======================
cake        | flour
cake        | eggs
cake        | milk
pie         | meat
pie         | peas
pie         | carrots

As you can see the recipe data is being repeated. Not such a problem here but if I have a recpe with 30 ingredients and 3K description (a little exesive I know😉) the database is going to be returning quite a lot of redundant data (87K). I was wondering if there was some wa of structuring the query so the results might look something like

recipe_name | ingredient_name
=======================
cake        | flour
[null]      | eggs
[null]      | milk
pie         | meat
[null]      | peas
[null]      | carrots

or maybe split the query down in some way so I'm not being returned the same data over and over again.

Thanks for your help so far.
Bubble

[nemonoman]

I was wondering if there was some way of structuring the query so the results might look something like [this example]

No.

What you can do is handle the info when returning the values:

$mycurrentrecipe='';
while($returnedRow=mysql_fetch_array($result)){
  if($mycurrentrecipe==$returnedRow['recipe.name']){   echo "null", $returnedRow['ingredient.name']
}
else{ 
echo $returnedRow['recipe.name'], $returnedRow['ingredient.name']
$mycurrentrecipe=$returnedRow['recipe.name'];
}//end else
}//end while

[bubblenut]

Hmmm, I though that might be the answer. I realized I could handle it afterwords in PHP but was just wondering if there was a way optmizing within MySQL. It seems a hole in the relational model which I'm sure must cause some relatively large overheads. I did think of one way around it which would require to queries and therefore two iterations of similar resultsets.

First pull out the recipe data and then run the same query again just pulling out the ingredients data and the recipe_id, and then amalgamate the two resultsets in PHP. I doubt this is going to be any more efficient unless I have huge descriptions which I don't think is very likely.

Thank you very much for helping me to clear this up nemonoman.

Bubble

[Drakla]

I know this might be absoutely useless, but if you GROUP BY on the recipe name you'll just get one recipe, but not the data returned like the thing with the nulls in.

[bubblenut]

returning all the ingredients is kinda vital in this scenario though 🙁

NB:
Generally safer to GROUP BY clauses on unique_id's because there could (legally withing the table schema) be two recipes with the same name.