SQL CROSS JOIN with JPA Specification

Question

I have a Spring Boot project which has three entity classes like the following,

@Entity
@Table("item")
public class Item extends SomeParent {

     @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true, mappedBy = "item")
     @JsonManagedReference("product-s")
     Set<Product> products;
}

@Entity
@Table(name = "product")
public class Product extends SomeParent {

     @ManyToOne
     @JoinColumn(name = "item_id", nullable = false)
     @JsonBackReference("product-s")
     private Item item;

     @ManyToOne
     @JoinColumn(name = "fruit_id", nullable = false)
     private Fruit fruit;

     private String type;
}

@Entity
@Table(name = "fruit")
public class Fruit extends SomeParent {

     private String name;

     private Integer qty;
}

I am using JPA Specification to build a query to find items by fruit.name and product.type. Following are my search queries,

1. fruit = mango, type = A and fruit = apple, type = B 
2. fruit = mango, type = A or fruit = apple, type = B 

By the 1st query it needs to search all the items by fruit.name = mango and product.type = A and fruit.name = apple and product.type = B. Item must have both mango with A category and apple with B category.

By the 2nd query it needs to search all the items by fruit.name = mango and product.type = A or fruit.name = apple and product.type = B. If any item has mango with A category or apple with B category then it should return the item

public Specification<Item> search() {
    return (root, query, criteriaBuilder) -> {
       SetJoin<Item, Product> productJoin = root.joinSet("products", JoinType.LEFT);

       if (isAndQuery) { 
          criteriaBuilder.and(
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "mango"), 
                    criteriaBuilder.equal(productJoin.get("type"), "A")));
          criteriaBuilder.and(
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "apple"), 
                    criteriaBuilder.equal(productJoin.get("type"), "B")));
       } else {
          criteriaBuilder.or(
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "mango"),
                    criteriaBuilder.equal(productJoin.get("type"), "A")));
          criteriaBuilder.or(
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "apple"),
                    criteriaBuilder.equal(productJoin.get("type"), "B")));
       }

       return criteriaBuilder;
    };
}

My issue is and query is not working, so it is not giving me the results. So can anybody help me to resolve this issue? What is I am doing wrong here?

This is the JPA generated AND query,

select * from item item
left outer join product product on item.id=product.item_id 
cross join fruit fruit
where product.fruit_id=fruit.id
and ((fruit.name='mango')
and product.type='A'
and (fruit.name='apple')
and product.type='B')
order by item.id asc limit ?

This is the JPA generated OR query,

select * from item item 
left outer join product product on item.id=product.item_id 
cross join fruit fruit
where product.fruit_id=fruit.id
and ((fruit.name='mango')
and product.type='A'
or (fruit.name='apple')
and product.type='B')
and 1=1
order by
item.id asc limit ?

Answer 1

AND won't work because you are performing the filter check on the same product/fruit as the other checks. A product's fruit can't be both an apple and a mango. The solution is to manually create two separate, explicit joins

public Specification<Item> search() {
   return (root, query, criteriaBuilder) -> {
       Predicate returnPredicate;
       SetJoin<Item, Product> productJoin = root.joinSet("products", JoinType.LEFT);

       if (isAndQuery) { 
          SetJoin<Item, Product> productJoin2 = root.joinSet("products", JoinType.LEFT);
          returnPredicate = criteriaBuilder.and(
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "mango"), 
                    criteriaBuilder.equal(productJoin.get("type"), "A"))),
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin2.get("fruit").get("name"), "apple"), 
                    criteriaBuilder.equal(productJoin2.get("type"), "B")));
       } else {
          returnPredicate = criteriaBuilder.or(
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "mango"),
                    criteriaBuilder.equal(productJoin.get("type"), "A"))),
               criteriaBuilder.and(
                    criteriaBuilder.equal(productJoin.get("fruit").get("name"), "apple"),
                    criteriaBuilder.equal(productJoin.get("type"), "B")));
       }

       return returnPredicate;
   };
}

This translates more into give me the Item that has both a product with an Apple and a separate product with a mango.