JPA sorting query by property of last element in child collection

Question

Say I have the following entities

class Order {

    @Id
    private long id;

    private long orderNo;

    @OneToMany
    @JoinColumn(name="order_id")
    @OrderBy("timeStamp ASC")
    private List<Item> items;
}

class Item {
    @Id
    private long id;

    // e.g. COMPLETED, IN PROGRESS, QUEUED, etc...
    private String status;

    private long timeStamp;

    @ManyToOne
    private Order order;
}

Using JPA I want to return a list of Orders that are sorted by the status from the last Item in the items list, which is the most recent one since items is order by timeStamp. How can this be done?

I strongly suspect this can't be done in a JPQL query. If I were you, I would either store the most recent item timestamp in the Order entity and sort by that (remembering to keep it updated), or implement a custom `Comparator` and use `Collections.sort` (sort in the Java code, not in a JQPL query). — Nick, Mar 22 '13 at 20:56

score 2 · Accepted Answer · answered Mar 25 '13 at 14:27

2

Something like,

Select o from Order o join o.items i where i.id = (Select max(timestamp) from Item i2 where i = i2) order by i.status

answered Mar 25 '13 at 14:27

James

17,965
11
91
146

JPA sorting query by property of last element in child collection

1 Answers1

Linked