I am trying to filter a query based on the values of all the objects on the left join. My objects are defined as such:
public class Recipe
{
public virtual long Id {get;set;}
public virtual ISet<Ingredient> Ingredients { get; set; } = new HashSet<Ingredient>();
}
public class Ingredient
{
public virtual long Id{get;set;}
public virtual string Ingredient {get;set;}
public virtual decimal Amount {get;set;}
}
And they are mapped like this:
<hibernate-mapping>
<class name="Recipe" table="Recipe">
<set name="Ingredients" inverse="true">
<key column="RecipeId" />
<one-to-many class="Recipe" />
</set>
</class>
</hibernate-mapping>
<hibernate-mapping>
<class name="Ingredient" dynamic-update="true" table="Ingredient" lazy="false">
<id name="Id" column="Id" type="long" unsaved-value="0">
<generator class="native" />
</id>
<property name="Ingredient" not-null="true" lazy="false"/>
<property name= "Amount" column= "Amount" type="decimal" />
</class>
</hibernate-mapping>
What I want to do is to be able to search a Recipe based on the ingredients. For example say I want to find all recipes where the ingredients are
Ingredient | Amount
Sugar | 100
Flour | 200
Eggs | 2
I am not really sure how I would even do that in SQL. I tried something like this:
public Recipe FindRecipeByIngredients(ISet<Ingredient> ingredients)
{
return this.Session.QueryOver<Recipe>()
.Where(r => r.Ingredients.All(c => ingredients.Any(i => i.Ingredient == r.Ingredient && i.Amount == r.ConcentrAmountation))).SingleOrDefault();
}
but Nhibernate doesn't know how to convert All into SQL. I was thinking it might be possible to do the search in reverse, searching in the Ingredient table for all records with the correct ingredient and amount, but then I would come up with all recipes which have those ingredients as well as others. Can this be done using SQL queries or do I have to accept the fact that I'll have to pull up all the records (with minimal filtering) and then filter them in the code?
(As a bonus I will also have to, in the future, filter similar recipes, so recipies in which the amounts are almost equal so maybe amount <1.05refAmount && amount >0.95refAmount)
Ok so I managed to find a fairly good solution this. The sql query I need to run is:
SELECT RecipeId
FROM Ingredient
GROUP BY RecipeId
HAVING SUM(CASE WHEN Ingredient = 'Ingredient1' AND Amount = 200 THEN 1 ELSE 0 END) = 1
AND SUM(CASE WHEN Ingredient = 'Ingredient2' AND Amount = 300 THEN 1 ELSE 0 END) = 1
AND ...
I had a couple more problems converting it into something nhibernate would construct correctly and ended up with this:
ICriteria criteria = Session.CreateCriteria<Ingredients>()
.SetProjection(Projections.GroupProperty(Projections.Property<Ingredient>(i => i.Recipe.Id)));
foreach (Ingredient ingredient in recipe.Ingredients)
{
criteria.Add(
Restrictions.Eq(
Projections.Sum(
Projections.Conditional(
Restrictions.Where<Ingredient>(i => i.Ingredient == ingredient.Ingredient && i.Amount == ingredient.Amount)
Projections.Constant(1),
Projections.Constant(0)
)
),
1));
}
Which returns the above query. I tried doing it with Restrictions.Conjuntction, or using QueryOver, but the conditional query ended up in a WHERE condition before the GROUP BY rather than the HAVING condition after the GROUP BY resulting in an incorrect sql query. It might be a bug in Nhibernate (similar to NH-2863), but I'm not sure. If anyone finds a more efficient way to solve this I'd me glad to update it. Also the answer is based on these answers on SO: