I'm using Hibernate 3 with MySQL 4.1 to map recipe data. I've got a recipe table and a recipe_nutrient table, which maps to nutrient data for the recipe. Recipes may or may not have nutrient data, and may map to some or all nutrients. The mapping in Recipe.hbm looks like:
Code:
..
<set
name="nutrientData"
table="recipe_nutrient">
<key column="recipe_id"/>
<composite-element class="com.makeamenu.data.recipe.RecipeNutrient">
<property
column="amount_per_serving"
name="amount"
not-null="true"
type="double"
length="22"
/>
<many-to-one
class="com.makeamenu.data.food.Nutrient"
column="nutr_no"
name="nutrient"
not-null="true"
/>
</composite-element>
</set>
..
I'm doing a search for recipes, in which I want to include the output of 3 particular nutrients, if the recipe has them, or null if the recipe doesn't. My HQL looks like (I've removed some extra stuff for brevity):
Code:
select recipe.id, recipe.name, nutrient291.amount, nutrient208.amount, nutrient204.amount from Recipe recipe join recipe.categories rc left join recipe.nutrientData nutrient291 left join recipe.nutrientData nutrient208 left join recipe.nutrientData nutrient204 where rc.id = 64 and nutrient291.nutrient.nutrNo = 291 and nutrient208.nutrient.nutrNo = 208 and nutrient204.nutrient.nutrNo = 204 order by recipe.name asc
I'm also adding a limit clause to the query, to only return 15 results at a time.
The problem I'm having is that because I'm selecting particular nutrients by ID in the where clause, if the recipe doesn't have those nutrients it isn't selected. If I remove the nutrient ID's in the where clause, it appears fine, but I loose the ability to select only those particular nutrients. What I want is for those nutrients to have values if the recipe has them, and be null if they don't. Any thoughts on how to solve this?
Login
FAQ
Search