CODEWITHSUNDEEP
javaspringhibernatejpa
ivaylo
ivaylo

Reputation: 841

One to many order by with case hibernate spring

I have a query that orders the entities by 2 dates:

@Query(value = "from Order order by CASE WHEN created > updated THEN created ELSE updated END desc")

Can I do the same with @OneToMany in the @OrderBy anotation?

@OneToMany(cascade = CascadeType.ALL ,mappedBy = "order", fetch = FetchType.EAGER)
@OrderBy(...)
private List<Dish> dishes = new ArrayList<>();

Also will this potentially take less time and performance compared to taking all orders and then looping over them and finding the dishes for each with a query?

Upvotes: 2

Views: 1564

Answers (2)

Ralph
Ralph

Reputation: 120831

Try to use org.hibernate.annotations.OrderBy instead of javax.persistence.OrderBy

The JavaDoc of org.hibernate.annotations.OrderBy claims:

Order a collection using SQL ordering (not HQL ordering).

Different from javax.persistence.OrderBy in that this expects SQL fragment, JPA OrderBy expects a valid JPQL order-by fragment.

This mean:

  • you can use SQL functions
  • you need to use the SQL column names instead of Java field names

Upvotes: 1

Farid Ahmed
Farid Ahmed

Reputation: 719

Hibernate supports the @OrderBy annotation which you can add to a relationship attribute as you can see in the following code snippet.

@OrderBy(value = "CASE DESC")

Upvotes: 1

Related Questions