JPQL maxResults on collection objs

Question

Hello

I have the following situation: a class Event that has a collection of Participant objects, but the class Participant don't have any reference to event. So, I need to get the first 5 most recent participants of a given event, just could not figure out how to this. I know that after get the query done I just need to limit the results by setting the maxResults in my entityManager. The reason to do this is because by getting all the participants all the time when I need to show just 5 is impacting application performance.

Using JPQL I can easily retrieve the events that have a given participant: select e from Event e where :participant in elements(e.participants) order by e.creationDate desc

But how could I select the participants of a given event, I mean in a way that give me the possibility to limit the number maxResults like in the query above?

Many thanks Thiago

Answer 1

Have you tried

SELECT p FROM Event e JOIN e.participants p 
WHERE e.id = :id ORDER BY p.participationTime DESC