I have the following structure:
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.
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: