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?
Have you tried
SELECT p FROM Event e JOIN e.participants p WHERE e.id = :id ORDER BY p.participationTime DESC