If There is a relation like this:
entity A (one) ---> (many) entity B (one)---->(one) entity C
And current JPA entity A has following relation definition:
@OneToMany(mappedBy = "A", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@OrderBy("b.c.fieldFromC DESC, field1FromB, field2FromB") // <--- b.c.fieldFromC DESC becomes a problem
private List<B> b_List;
Seems @OrderBy only valid if b is in a condition(because b is a field of A) but not for any fields of B(sub relations), in short, @OrderBy only valid for A.b but not A.b.c
We are using OpenJPA and I know provide like Hibernate can provide sorter so that can include a comparator class, but my question is, is there any spec about @OrderBy by sub relation in general JPA 2.0 spec? If not, then, does OpenJPA have any of those sorter implementation? What could be an alternative?
Yes, it is specified in JPA 2.0 specification - @OrdedBy does not support order by attributes of entities that are related to the entities in list:
A property or field name specified as an orderby_item must correspond to a basic persistent property or field of the associated class or embedded class within it. The properties or fields used in the ordering must correspond to columns for which comparison operators are supported.
The dot (".") notation is used to refer to an attribute within an embedded attribute. The value of each identifier used with the dot notation is the name of the respective embedded field or property.
One alternative is to create Comparator (sort in Java instead of SQL) and use it in custom method that returns list in preferred order.