CODEWITHSUNDEEP
javajpaormjpa-2.0
Piotr Sobczyk
Piotr Sobczyk

Reputation: 6583

JPA: ordering query results by collection valued property?

I have an entity that has dollection-valued property:

@Entity
public class Scenario{
  ..
  @ManyToMany
  List<Category> categories;
  ..
}

Is there any way to query for Scenarios and order query results for categories property, something like this:

select scenario from Scenario scenario order by scenario.category

More precisely, we are interested in sorting for first category from each Scenario's list and ignoring the rest of them. Can we do such things in JPQL/Criteria queries (eg. passing custom Comparator somehow)?

Upvotes: 2

Views: 1304

Answers (2)

Bharat Sinha
Bharat Sinha

Reputation: 14363

We use hibernate annotation to sort the data:

@Sort(type = SortType.NATURAL)
private SortedSet<Category> sections = new TreeSet<Category>();

Here we were using Set annotation as we wanted unique elements also.

public class Category implements java.io.Serializable, Comparable<Category> {

    public int compareTo(Category category) {    
        // Your implementation of compareTo
    }
}

Upvotes: 0

axtavt
axtavt

Reputation: 242686

Your question implies that categories collection has a defined order. If it's an indexed collection (i.e. it's annotated with @OrderColumn) and you want to sort Scenarios by name of the first Category, you should be able to do the following:

select s from Scenario s left join s.categories c 
where index(c) = 0 order by c.name

Upvotes: 2

Related Questions