JPQL query with WHERE on nested fields

Question

I have a java entity class UserBean with a list of events:

@OneToMany
private List<EventBean> events;

EventBean has Date variable:

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

Now in UserBean I want to create a NamedQuery that returns all dates that fall within 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 though. I get this error:

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

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

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

Answer 1

Path u.events.eventDate is an illegal construct in JPQL, because it is not allowed to navigate via a collection valued path expression. In this case u.events is a collection valued path expression. In JPA 2.0 specification this is told with following words:

It is syntactically illegal to compose a path expression from a path expression that evaluates to a collection. For example, if o designates Order, the path expression o.lineItems.product is illegal since navigation to lineItems results in a collection. This case should produce an error when the query string is verified. To handle such a navigation, an identification variable must be declared in the FROM clause to range over the elements of the lineItems collection.

This problem can be solved by using JOIN:

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