jpa criteria-api: join with subselect

2019-06-17 03:29发布

问题:

This query is used to retrieve last records in a one-to-many relationship (see SQL join: selecting the last records in a one-to-many relationship)

SELECT  p.*
FROM    customer c 
        INNER JOIN (
                      SELECT customer_id, MAX(date) MaxDate
                      FROM purchase
                      GROUP BY customer_id
                    ) MaxDates ON c.id = MaxDates.customer_id 
        INNER JOIN purchase p ON MaxDates.customer_id = p.customer_id
                    AND MaxDates.MaxDate = p.date;

My question: How can I build this join with the subselect with the jpa criteria-api? Is it possible? If not, possible with jpql?

My code so far:

final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
final CriteriaQuery<Purchase> query = cb.createQuery(Purchase.class);
final Root<CustomerEntity> root = query.from(Customer.class);

// here should come the join with the sub-select

final Path<Purchase> path = root.join(Customer_.purchases);
query.select(path);

final TypedQuery<Purchase> typedQuery = entityManager.createQuery(query);
return typedQuery.getResultList();