CODEWITHSUNDEEP
javajpahqljpql
stuff22
stuff22

Reputation: 1672

Ordering a join fetched collection in JPA using JPQL/HQL

Given the below JPQL statement, how do I modify it so that the kittens in the resulting list are ordered by their age property?

SELECT c FROM Cat c left join fetch c.kittens WHERE c.id = :id

I've tried multiple approches but without any luck. This is esentially what I would like to do, but it doesn't work:

SELECT c FROM Cat c left join fetch c.kittens k WHERE c.id = :id ORDER BY k.age

Upvotes: 17

Views: 14994

Answers (3)

Meet Shah
Meet Shah

Reputation: 19

I am retrieving time intervals with corresponding time slots, and able to order the records using the property of the child collection in the JPQL query.

Following works for me.

SELECT DISTINCT i FROM Interval i INNER JOIN FETCH i.slots s WHERE i.schedulerId = :schedulerId ORDER BY s.startDateTime

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691625

In addition to @bigZee77's answer, you could also perhaps change your query and query for the kitten instead of the cat. The resulting list of kittens would be ordered, and every kitten would point to the same cat:

select k from Cat c inner join fetch c.kittens k where c.id = :id order by k.age

If the cat doesn't have any kitten, you would get an empty list, though.

The alternative is of course to provide a Java method which sorts the list of kittens.

Upvotes: 5

bigZee77
bigZee77

Reputation: 340

Hej,

I don't think this is possible when applied using queries. But as far as I remember, you can use this to add default ordering to your collection in the mapping:

@OrderBy("myColumName asc")

Upvotes: 14

Related Questions