So I have next situation. I need to check if entity with ManyToMany relationship exists by list of this entities. Example:

@Entity
@NoArgsConstructor
@Builder(setterPrefix = "with")
@AllArgsConstructor
@Getter
@Setter
@Table(name = "ingredient")
public class Ingredient  {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "ingredient_generator")
    @SequenceGenerator(name = "ingredient_generator", sequenceName = "ingredient_id_seq",
            allocationSize = 100,initialValue = 1000)
    private Long id;

    @Column(name = "name",unique = true,nullable = false)
    private String name;

    @Column(name = "price",nullable = false)
    private Integer price;

    @Column(name = "loss_probability",nullable = false)
    private Short lossProbability;

    @ManyToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY)
    @JoinTable(
            name = "recipe",
            joinColumns = { @JoinColumn(name = "recipe_ingredient_id",referencedColumnName = "id",nullable = false) },
            inverseJoinColumns = { @JoinColumn(name = "ingredient_id",nullable = false) }
    )
    private List<Ingredient> ingredients;


} 

This is my entity, and I need to check if Ingredient with same ingredients already exists. Like this: Ingredient made from ingredients with ids [1,2], and I want to get true if ingredient from ids [1,2] exists, and if I have no Ingredient with [1,3], I want go get false. But in my example:

@Query(value =
            "SELECT CASE WHEN r.ingredient_id IN(?1) THEN TRUE ELSE FALSE END "  
                    "FROM ingredient i JOIN recipe r ON i.id = r.recipe_ingredient_id "  
                    "WHERE r.ingredient_id IN (?1) "  
                    "GROUP BY r.ingredient_id ",nativeQuery = true)
    List<Boolean> existIngredientsByIngredients(List<Ingredient> ingredients);

I got true, even with one coincidence, example: I have Ingredient from ingredients [1,3], and checking by ingredient ids [1,4], and it's return me true, cuz in my ingredient I got id -> 1, but it's should return false cause their no Ingredient created from ingredients [1,4] but only from [1,3]. P.S. method generated from data jpa existsByIngredientsIn dont work as I want, cuz it's too return true even with one coincidence. I really don't understand how I can write this query.

CodePudding user response：

One possible idea to solve this problem would be to count the matching rows for your searched ingredients (recipe_ingredient_id) in the receipt table grouped by the ingredient_id. Then you could use that count to verify if any matching receipt/ingredient for the given ingredients exists.

If it is possible/acceptable for you to add another parameter to your method. The query could look like the following. With ?1 = list of searched ingredient ids and ?2 = number of searched ingredient ids (or size of list).

select 
  case 
    when matches > 0 then true 
    else false 
  end
from (
  select count(r.recipe_ingredient_id) matches
    from recipe r
    where r.ingredient_id in (?1)
    group by r.recipe_ingredient_id
    having count(r.ingredient_id) = ?2
) result;

Otherwise if you want/need to stick with one parameter, you could use an additional subquery to get that number directly in the SQL query.

select 
  case 
    when matches.count = total.count then true 
    else false 
  end
from (
  select r.recipe_ingredient_id count(r.ingredient_id) count
    from recipe r
    where r.ingredient_id in (?1)
    group by r.recipe_ingredient_id
) matches join (
  select r.recipe_ingredient_id count(r.ingredient_id) count
    from recipe r
) total on matches.recipe_ingredient_id = total.recipe_ingredient_id;

CodePudding user response：

In the end, I solved this problem with the following ugly solution:

@Query("SELECT i "  
            "FROM Ingredient i "  
            "WHERE i.ingredients.size = ?1 ")
    List<Ingredient> findIngredientByIngredients(Integer size);

    default boolean existsAllByIngredients(List<Ingredient> ingredients) {

        List<Ingredient> ingredientsWithRequiredIngredientsSize = findIngredientByIngredients(ingredients.size());

        for (Ingredient ingredientWithRequiredIngredientsSize : ingredientsWithRequiredIngredientsSize) {
            List<Ingredient> ingredientsFromIngredientCreated = ingredientWithRequiredIngredientsSize.getIngredients();
            int equalsCount = 0;
            for (Ingredient ingredientFrom : ingredientsFromIngredientCreated) {
                for (Ingredient checkIngredient : ingredients) {
                    if (checkIngredient.equals(ingredientFrom)) {
                        equalsCount  ;
                    }
                }
                if (equalsCount == ingredients.size()) {
                    return true;
                }
            }
        }
        return false;
    }