multiple join on @OneToMany with condition on each

I have the following structure:

Product.Java :

String name

@OneToMany(mappedBy="product")
private List<ProductServingTimeAvailablity> servingTimeAvailablities;

@OneToMany(mappedBy="product")
private List<ProductStyles> styles;

@OneToMany(mappedBy="product")
private List<ProductLocationAvailability> locationAvailabilites;

I need to retrieve all the products where

  • product.servingTimeAvailablities contains 1 and
  • product.styles contains 2 and
  • product.locationAvailabilites contains 3

(meaning – each product can have many time availability and many styles and many locations and I need to choose only the products that are available at time 1, are style 2 and are available for location 3)

How can I do it in HQL?

It’s not in but contains.

Answer

Something like the following should work.

SELECT product FROM Product product
    join product.servingTimeAvailablities as servingTimeAvailablities
    join product.styles as styles
    join product.locationAvailabilites as locationAvailabilites
    WHERE servingTimeAvailablities.someProperty = :somePropertyValue1
    AND styles.someProperty = :somePropertyValue2
    AND locationAvailabilites.someProperty = :somePropertyValue3

I don’t know the internals of your objects, which is why I used ‘someProperty’ as a field name. You should replace it with the proper field name that you want to match. I also used ‘join’ as the join type for the example. You should replace that with the join type that you want to use (e.g. inner join, left outer join, etc).

Let me know if that works for you. I Haven’t tried it myself 🙂

References that helped me were:

http://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/queryhql.html#queryhql-select

HQL: illegal attempt to dereference collection

Leave a Reply

Your email address will not be published. Required fields are marked *