CODEWITHSUNDEEP
jpaspring-datajpql
nagy.zsolt.hun
nagy.zsolt.hun

Reputation: 6694

JPQL - filter based on last updated timestamp attribute inside @ElementCollection elements

I have @Entity-s (Order) that may change their state. Each state-change is represented with an @Embeddable object (OrderUpdate), added to an @ElementCollection (Order.updates). How can I query Orders based on the most recent update?

My goal is to select those Orders, where the last update's state is DONE.

Order:

@Entity
public class Order {

    @Id
    @GeneratedValue
    private UUID id;

    @ElementCollection
    private List<OrderUpdate> updates;
    ...

OrderUpdate:

@Embeddable
public class OrderUpdate {

    @ManyToOne
    private User host;

    private String state;

    @CreationTimestamp
    private Timestamp timestamp;
    ...

OrderRepository:

@Repository
public interface OrderRepository extends CrudRepository<Order, UUID> {

    @Query("select o from Order o where [o.updates.last.state TODO] = DONE")
    Set<Order> findDone();

}

Upvotes: 0

Views: 184

Answers (1)

osama yaccoub
osama yaccoub

Reputation: 1866

@Query("select o from Order o inner join o.updates u where u.state = ''DONE'' and u.timestamp = (select max(u2.timestamp) from Order o2 inner join o2.updates u2 where o2.id = o.id)) If I understand correclty

Upvotes: 1

Related Questions