CODEWITHSUNDEEP
javahibernatejpa
Brian Deacon
Brian Deacon

Reputation: 21962

Ordering return of Child objects in JPA query

So if my JPA query is like this: Select distinct p from Parent p left join fetch p.children order by p.someProperty

I correctly get results back ordered by p.someProperty, and I correctly get my p.children collection eagerly fetched and populated. But I'd like to have my query be something like "order by p.someProperty, p.children.someChildProperty" so that the collection populated inside each parent object was sub-ordered by someChildProperty.

This seems intuitive when I think in terms of the sql that is actually generated for these calls, but I guess less so when it tries to map back to hierarchical objects.

Upvotes: 9

Views: 19758

Answers (4)

eebbesen
eebbesen

Reputation: 5148

Using org.springframework.data.domain.Sort you can implement this when calling methods in your repository class that extends org.springframework.data.jpa.repository.JpaRepository.

For example, assuming the Teacher entity is a child of School and the Student entity is a child of Teacher

teacherRepository.findAll(Sort.by("school.name", "student.lastName"))

will sort results by the parent entity's name attribute, then by the child entity's lastName attribute.

NOTE: Adding the child attribute to the sort will of course force a join to the child table in the generated query. In this case, if you have more than one Student entity with the same lastName attribute the number of records returned to be greater unless you explicitly ask for distinct records.

Upvotes: 0

Dilli Babu Kadati
Dilli Babu Kadati

Reputation: 169

If you are using springboot and jpa here's the example

in parent class add the code like below

@OneToMany(mappedBy = "user")
@OrderBy(value = "position desc ")
private List<UserAddresses> userAddresses;

in the above code, position is the field name in UserAddresses Class and desc is the order. You can pass either asc or desc like in sql order by.

Upvotes: 6

Adeel Ansari
Adeel Ansari

Reputation: 39887

For preserving order, use TreeSet. As far as, sorting of a collection inside parent is concerned, just do it in your code using Comparator.

Well, try this on your collection definition in your parent entity class. I hope you are getting my point.

You can use this JPA annotation,

@javax.persistence.OrderBy(value = "fieldName")

or this Hibernate specific,

@org.hibernate.annotations.OrderBy(clause = "FIELD_NAME asc")

and you can also use this,

@org.hibernate.annotations.Sort(type = SortType.NATURAL)

or

@org.hibernate.annotations.Sort(type = SortType.COMPARATOR)

In the case of comparator, a comparator must be in place. Other might only work with String collections.

Upvotes: 18

cliff.meyers
cliff.meyers

Reputation: 17734

Adeel basically has this nailed. You can also use those with a List which might be important if your collections contain the same entity more than once.

Upvotes: 1

Related Questions