java - JPQL select all objects, but setMaxResult on object collections -

java - JPQL select all objects, but setMaxResult on object collections -

i have 3 entities:

user has set galleries. gallery has set photos.

now want select users lastly 3 photos created time(user may have 10 galleries , 100 photos each gallery). how can jpql?

i don't know how top 3 photos(select top 3 p) below:

query q = em.createquery("select u, (select top 3 p) user u left bring together u.galleries g left bring together g.photos p");

i know can 1 user top 3 photos by:

query q = em.createquery("select u, p user u left bring together u.galleries g left bring together g.photos p u.id =:userid").setmaxresults(3);

but users in 1 jpql?

thanks in advance.

you happen work on postgresql can utilize window function select first 3 photo.ids user:

list<long> photoids = (list<long>) em.createnativequery( "select data.p_id ( select p.id p_id, row_number() rw on w rw photo p inner bring together galley g on g.id = p.galery_id inner bring together user u on u.id = g.user_id window w (partition u.id order p.creation_time desc) ) info rw <= 3", long.class) .getresultlist();

then these ids can fetch photo/gallery/user:

list<photo> photos = (list<photo>) em.createquery( "select p photo p bring together fetch p.gallery g bring together fetch g.user u p.id in (:photoids)") .setparameter("photoids", photoids) .getresultlist();

you can recreate user/gallery/photo photo list.

there no other way utilize jpql select partial views on joined collection of children entities.

java hibernate orm jpql