I have a Product Entity like below (It's simple version)

@Entity
@Table(name = "product")
public class Product {
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   private long id;

   @OneToMany(mappedBy = "product")
   private List<ProductAtt> attributes;
}

Each Product could have one or more Attribute. Attribute look likes below

@Entity
@Table(name = "attribute")
public class Attribute {
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   private long id;

   private String name;
}

So I create a relation entity like below with extra value property

@Entity
@Table(name = "product_att")
public class ProductAtt implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @ManyToOne
    @JoinColumn
    private Product product;

    @ManyToOne
    @JoinColumn
    private Attribute attribute;

    private int value;
}

Now I want to find all products that have some attributes with custom values. For example all products that have attribute 1 with value 3 and attribute 2 with value 40 and ... .

What is the simplest and most efficient query to do that?

    
depends on how many attributes you want to check – XtremeBaumer yesterday
    
It's a list of attributes, from one to many attributes – vakarami yesterday
    
best way would be to use Criteria for this i think. if you want to check for every attribute and not for example for attribute 1 and 3 and 5... then you just iterate the list and ask for the values and add a Predicate to the criteria and in the end you can execute the query. this might not be the best solution but the easiest i can think of as you have variable attributes to check – XtremeBaumer yesterday

1 Answer 1

Since the number of attributes to query is not known at design time, one of the dynamic query mechanisms supported by Spring Data JPA will have to be used. The query can certainly be built using the JPA Specification or Criteria APIs.

If using QueryDSL support, subqueries with exists can be used. The following example shows how this can be done (assuming Java 8 and QueryDSL 4).

interface ProductRepository
          extends CrudRepository<Product, Long>
                  , QueryDslPredicateExecutor<Product> {
  default Iterable<Product> findAllByAttributes(final Map<String, String> attributes) {
    final QProduct root = QProduct.product;
    BooleanExpression query = root.isNotNull();

    for (final String attribute : attributes.keySet()) {
      final QProductAttribute branch = root.attributes.any();
      final BooleanExpression subquery = branch.attribute.name.equalsIgnoreCase(attribute)
                                                          .and(branch.value.equalsIgnoreCase(attributes.get(attribute)));

      query = query.and(JPAExpressions.selectFrom(QProductAttribute.productAttribute).where(subquery).exists());
    }

    return findAll(query);
  }
}

It should be noted that the database design is such that performance problems are bound to happen, because the same table (ProductAttr) is included as many times as there are attributes to search by. This is not a problem of QueryDSL, JPA, Hibernate, SQL or the database server but the data model itself (also known as the EAV model).

    
Tnx a lot for the comprehensive answer. But It seams a very slow query, I think I should avoid to use this data model and use null columns instead, to gain speed – vakarami 2 hours ago