JPA @OrderBy() Through Relational Table

Question

I need some help with the JPA Framework. I've read some answers "kind of" about this topic but I couldn't reach any conclusion.

First heres an examplo of the design i'm wooking with.

@BusinessObject
public class ClassA {

    @Column(name = "ID", nullable = false)
    private Long id;

    @OneToMany(mappedBy = "classAAttr")
    private Collection<ClassAB> classABCollection;

    //STUFF AND OTHER COLUMNS.....
}

public class ClassAB {

    @Column(name = "ID", nullable = false)
    private Long id;

    @JoinColumn(name = "TABLE_A_ID", referencedColumnName = "ID")
    @ManyToOne
    private ClassA classAAttr;

    @JoinColumn(name = "TABLE_B_ID", referencedColumnName = "ID")
    @ManyToOne
    private ClassB classBAttr;

    //STUFF AND OTHER COLUMNS.....
}

@BusinessObject
public class ClassB {

    @Column(name = "ID", nullable = false)
    private Long id;

    @Column(name = "ORDERCLAUSE", nullable = false)
    private String orderClause;

    //STUFF AND OTHER COLUMNS.....
}

So I need to order the classABCollection in ClassA by the orderClause attribute in ClassB, but I can't find the right @OrderBy() clause AND/OR location for it.

I've read some things about the Comparator Interface but, unfortunately, due to business policy, I need to be sure that there is no other way...

How Should I Do It?

Thank you guys in advance.

Answer 1

So - I found a roundabout. A query like one below wont work:

select * from A a  where xyz 
order by a.reference or a.reference.id 

However, I found that by adding a function, we can make the query work: (Do note, the reference need not be null.. and use appropriate values.)

select * from A a  where xyz 
order by coalesce(a.reference , 0)