JPQL query with WHERE on nested fields

Question

I have a UserBean Java class class with a list of events:

@OneToMany private List<EventBean> events; 

EventBean has a Date variable:

 @Temporal(javax.persistence.TemporalType.TIMESTAMP) private Date eventDate; 

Now in UserBean I want to create a NamedQuery that returns all dates that belong to a specific range:

 @NamedQuery(name="User.findEventsWithinDates", query="SELECT u.events FROM UserBean u WHERE u.name = :name AND u.events.eventDate > :startDate AND u.events.eventDate < :endDate") 

The above query does not compile. I get this error:

 The state field path 'u.events.eventDate' cannot be resolved to a valid type. 

By the way, I am using EclipseLink version 2.5.0.v20130507-3faac2b.

What can I do to make this request work? Thanks.

Answer 1

The u.events.eventDate path is an illegal construct in JPQL because it is not allowed to navigate through the path expression given the collection. In this case, u.events is an expression for a set of values. The JPA 2.0 specification says this with the following words:

It is syntactically illegal to compose a path expression from an expression path that is evaluated in the collection. For example, if o stands for Order, the o.lineItems.product path expression is illegal because navigating to a string results in a collection. This case should throw an error while checking the query string. To handle such navigation, an identification variable must be declared in FROM to move through the elements of the lineItems collection.

This problem can be solved using JOIN:

 SELECT distinct(u) FROM UserBean u JOIN u.events e WHERE u.name = :someName AND e.eventDate > :startDate AND e.eventDate < :endDate