JPQL to select all objects, but with setMaxResult on object collections

Question

I have 3 entities:

User which has Set galleries. Gallery which has Set photos.

Now I want to select all users with their last 3 photos by created time(User may have 10 galleries and 100 photos for each gallery). How can I do the jpql?

I don't know how to get the top 3 photos(select top 3 from p) below:

Query q = em.createQuery("select u, (select top 3 from p) from User u left join u.galleries g left join g.photos p");

I know I can get only one user with top 3 photos by:

Query q = em.createQuery("select u, p from User u left join u.galleries g left join g.photos p where u.id =:userId").setMaxResults(3);

But what about all users in one jpql?

Thanks in advance.

Answer 1

You happen to work on PostgreSQL, so you can use a window function to select the first 3 photo.ids by user:

List<Long> photoIds = (List<Long>) em.createNativeQuery("""
    select data.p_id
        from 
        (
            SELECT p.id as p_id, row_number() as rw OVER w as rw
            FROM photo p
            INNER JOIN galley g on g.id = p.galery_id
            INNER JOIN user u on u.id = g.user_id
            WINDOW w AS (PARTITION BY u.id ORDER BY p.creation_time DESC)
        ) data 
        where rw <= 3
        """, Long.class)
.getResultList(); 

Then with these photoIds, you can fetch the photo/gallery/user:

List<Photo> photos = (List<Photo>) em.createQuery("""
    select p
    from Photo p
    join fetch p.gallery g
    join fetch g.user u
    where p.id in (:photoIds)
    """)
.setParameter("photoIds", photoIds) 
.getResultList();

You can then recreate the user/gallery/photo back from the Photo list.

There is no other way to use JPQL to only select partial views over a joined collection of children entities.