2

I have a class Video that I'm querying for in my Hibernate and I am ordering the results by Order.desc("id"). The query works as expected. However, if I add a @OneToMany annotation in Video to include the comments, I also add an @OrderBy to that same annotation (I need to the comments to be ordered by "createdTime"). 

@OneToMany(fetch = FetchType.LAZY, mappedBy = "videoId", cascade = CascadeType.ALL, orphanRemoval = true)
@OrderBy("commentTime")
public List<Comment> getComments()

This breaks the main query - the return of videos is now wrong: it orders SQL to return videos with no comments first, followed by 1 comment, etc:

order by comments6_.commentTime asc, this_.videoId desc

I need to only sort videos by their ids.

Avinash T.
  • 2,280
  • 2
  • 16
  • 23
tumanov
  • 539
  • 1
  • 7
  • 14
  • Perhaps it might be worth using Hibernate's `@Sort` instead which performs the ordering in-memory after it's retrieved the data? You will need to provide a `Comparator` for this though. At least this way you know it won't affect your SQL. – Tony Day Jan 08 '13 at 14:21

1 Answers1

4

Try using @Sort instead:

@OneToMany(fetch = FetchType.LAZY, mappedBy = "videoId", cascade = CascadeType.ALL, orphanRemoval = true)
@Sort(type = SortType.COMPARATOR, comparator = CommentTimeComparator.class)
public List<Comment> getComments()

This way you know it won't affect your SQL. It may also offer a performance improvement by taking pressure off your database - for example, if you have a number of @OneToMany associations on the same entity that you want sorting.

Tony Day
  • 2,170
  • 19
  • 25