Hello there! We have the following relationship:



Is it possible to use HQL to query for this:

for each Category, fetch its children
for each children fetch its medias
return the joining of each category's child medias into one big list

I know I could do this as the pseudo code above, using a for loop, and while the session was opened I would query for it. But it seems to be such an impact on the DB. I wonder if there's a better way to do this

Regards

Query query = session.createQuery("select category.id,medias from Category category left join category.children child left join child.medias medias");
List x = query.list();

each element in this collection will be an array of length two. In that array, first element will have the category id and second element will have the all the medias of it's child catergoies.


or If you run the following it will give you list of medias(there won't be an array)

Query query = session.createQuery("select medias from Category category left join category.children child left join child.medias medias");
List x = query.list(); where x is a list of Media objects

With "select category.id,medias from Category category left join category.children child left join child.medias medias" I get a list of arrays which second element is a unique media. Categories having many medias are split among as many lines as their medias count.

Isn't there some way to have the medias grouped by category with a unique HQL query?