0

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.

Anda Iancu
  • 530
  • 1
  • 3
  • 9
Dejell
  • 13,947
  • 40
  • 146
  • 229

1 Answers1

1

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

Community
  • 1
  • 1
mess
  • 121
  • 1
  • 6
  • Thanks, I want only the product table data. What join shall I use? – Dejell Aug 31 '13 at 19:03
  • In your case, I believe the 'inner join' is the one that you need. The example I provided above uses inner join (note that you can abbreviate 'inner join' by just writing 'join' in HQL, as I did in the example). See http://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/queryhql.html#queryhql-joins for more info. – mess Sep 01 '13 at 05:06
  • Executing a query similar to the example that I provided in the answer should get you all the products that match the specified criteria. FYI: If you are already familiar with SQL, maybe the following reference might help a little bit in understanding the different type of joins that exists in SQL: http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join – mess Sep 01 '13 at 05:15