How to sort through an association table in hibernate?

Question

Given a representation of database tables where two entities are connected through a join table, is it possible to sort on the other entity specifying the sort order somehow through annotations?

Consider, roughly, the following database tables:

actor ( id, name )
credit ( actorId, movieId, roleName )
movie ( id, title, year )

And hibernate entities like:

@Entity
class Actor {
    @Id
    Integer id;
    @Column
    String name;
    @OneToMany
    @JoinColumn(name = "actorId")
    List<Credit> credits;
}

@Entity
class Credit {
    @Column
    String roleName;
    @ManyToOne
    @JoinColumn(name = "movieId")
    Movie movie;
}

@Entity
class Movie {
    @Id
    Integer id;
    @Column
    Integer year;
    @Column
    String title;
}

So I'd like to be able to have the default sorting order return Credits for an Actor in descending order by the year of the movie:

actor.getCredits(); // Returns credits in order of Credit.movie.year

It seems like one level deep of joining is pretty straightforward to sort by using @OrderBy, for example @OrderBy("roleName") ... but how do I go another join deeper?

Thanks!

Answer 1

You could also achieve this at query time using an order by clause as shown here: http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-ordering

Answer 2

Hi,

According to JPA specification:

The property or field name must correspond to that of a persistent property or field of the associated class

It explains why @OrderBy("movie.year") does not work as expected

Unless you use a HQL query, you should use encapsulation to get your goal

@Entity
public class Actor {

    private List<Credits> credits = new ArrayList<Credits>();

    @OneToMany
    @JoinColumn(name="ACTOR_ID")
    public List<Credits> getCredits() {
        return this.credits;
    }

    @Transient
    public List<Credits> getCreditsOrderedByMovieYear() {
        Collections.sort(credits, new Comparator<Credits>() {
            public int compare(Credits o1, Credits o2) {
                // Integer implements Comparable<T>
                return o1.getMovie().getYear().compareTo(o2.getMovie().getYear());
            }
        });

        return credits;
    }

}

Sometimes, when Hibernate does not provide some custom behavior, i use encapsulation to get my goal

regards,