Hibernate OneToMany OrderBy messes up main query

Question

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.

Answer 1

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.