1

I have 3 tables: Recipe, Ingredient and RecipeIngredient with the following relation:

Recipe-1:n-RecipeIngredient-n:1-Ingredient.

RecipeIngredient looks like:

id | recipe_id | ingredient_id | value
1  | 1         | 1             | 200
2  | 1         | 2             | 0.2
3  | 2         | 1             | 140
4  | 2         | 4             | 20

Ingredient looks like:

id | name   |
1  | Apple  |
2  | Banana |
3  | Orange |
4  | Lemon  |

recipe_id and ingredient_id are FKs.

Now I want to check, if the database has already one recipe with the same ingredient names and values (to avoid duplicate recipes).

My function gets a List of Strings and floats for the names and the values.

My current approach does not pay attention to check the value, but it already doesn't work.

// _IngredientNames == List of Strings with the names
// _IngredientValues == List of floats with the values
Criteria RecipeCriteria = DBSession.createCriteria(DBRecipeIngredient.class, "RecipeIngredient");
RecipeCriteria.createAlias("RecipeIngredient.ingredient", "i");
RecipeCriteria.add(Restrictions.in("i.name", _IngredientNames));

int NumberOfRecipes = RecipeCriteria
          .setProjection(Projections.projectionList()
              .add(Projections.groupProperty("RecipeIngredient.recipe")))
          .list().size();

Examples: If I say, I have Apple 200 and Banana 0.2, NumberOfRecipes should be 1. If I had Apple 200 and Banana 0.3, NumberOfRecipes should be 0. Or if I had Apple 10 and Lemon 3, NumberOfRecipes should also be 0.

I hope someone can help.

Thanks!

Fabian
  • 63
  • 1
  • 8
  • I am afraid it's not possible to achieve with pure hibernate. As the first thing I would add HAVING COUNT(*)=_IngredientNames.size(). You can use e.g. http://fokot.blogspot.com.by/2011/06/having-clause-in-hibernate-criteria-no.html or simple SQL projection http://stackoverflow.com/questions/8605708/how-to-use-having-count-with-hibernate – StanislavL Jan 08 '16 at 11:39

0 Answers0