multiple join on @OneToMany with condition on each Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of multiple join on @OneToMany with condition on each without wasting too much if your time.

The question is published on by Tutorial Guruji team.

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

We are here to answer your question about multiple join on @OneToMany with condition on each - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji