Hibernate entity query for finding the most recent, semi-unique row, in a single table

Question

I have a Hibernate database with a single table that looks like:

PURCHASE_ID | PRODUCT_NAME | PURCHASE_DATE | PURCHASER_NAME | PRODUCT_CATEGORY
------------------------------------------------------------------------------
     1          Notebook      09-07-2018          Bob            Supplies
     2          Notebook      09-06-2018          Bob            Supplies
     3           Pencil       09-06-2018          Bob            Supplies
     4            Tape        09-10-2018          Bob            Supplies
     5           Pencil       09-09-2018         Steve           Supplies
     6           Pencil       09-06-2018         Steve           Supplies
     7           Pencil       09-08-2018         Allen           Supplies

And I want to return only the newest purchases, based on some other limitations. For example:

List<Purchase> getNewestPurchasesFor(Array<String> productNames, Array<String> purchaserNames) { ... }

Could be called using:

List<Purchase> purchases = getNewestPurchasesFor(["Notebook", "Pencil"], ["Bob", "Steve"]);

In English, "Give me the newest purchases, for either a Notebook or Pencil, by either Bob or Steve."

And would provide:

PURCHASE_ID | PRODUCT_NAME | PURCHASE_DATE | PURCHASER_NAME
-----------------------------------------------------------
     1          Notebook      09-07-2018          Bob            
     3           Pencil       09-06-2018          Bob            
     5           Pencil       09-09-2018         Steve           

So it's like a "distinct" lookup on multiple columns, or a "limit" based on some post-sorted combined-column unique key, but all the examples I've found show using the SELECT DISTINCT(PRODUCT_NAME, PURCHASER_NAME) to obtain those columns only, whereas I need to use the format:

from Purchases as entity where ...

So that the model types are returned with relationships intact.

Currently, my query returns me all of the old purchases as well:

PURCHASE_ID | PRODUCT_NAME | PURCHASE_DATE | PURCHASER_NAME | PRODUCT_CATEGORY
------------------------------------------------------------------------------
     1          Notebook      09-07-2018          Bob            Supplies
     2          Notebook      09-06-2018          Bob            Supplies
     3           Pencil       09-06-2018          Bob            Supplies
     5           Pencil       09-09-2018         Steve           Supplies
     6           Pencil       09-06-2018         Steve           Supplies

Which, for repeat purchases, causes quite the performance drop.

Are there any special keywords I should be using to accomplish this? Query languages and SQL-fu are not my strong suits.

Edit:

Note that I'm currently using the Criteria API, and would like to continue doing so.

Criteria criteria = session.createCriteria(Purchase.class);
criteria.addOrder(Order.desc("purchaseDate"));
// Product names
Criterion purchaseNameCriterion = Restrictions.or(productNames.stream().map(name -> Restrictions.eq("productName", name)).toArray(Criterion[]::new));
// Purchaser
Criterion purchaserCriterion = Restrictions.or(purchaserNames.stream().map(name -> Restrictions.eq("purchaser", name)).toArray(Criterion[]::new));
// Bundle the two together
criteria.add(Restrictions.and(purchaseNameCriterion, purchaserCriterion));

criteria.list(); // Gives the above results

If I try to use a distinct Projection, I get an error:

ProjectionList projections = Projections.projectionList();
projections.add(Projections.property("productName"));
projections.add(Projections.property("purchaser"));
criteria.setProjection(Projections.distinct(projections));

Results in:

17:08:39 ERROR Order by expression "THIS_.PURCHASE_DATE" must be in the result list in this case; SQL statement:

Because, as mentioned above, adding a projection/distinct column set seems to indicate to Hibernate that I want those columns as a result/return value, when what I want is to simply limit the returned model objects based on unique column values.

Answer 1

First, use aggregation query to get last purchase date for product + purchaser combination.

Use that query as subselect matching the tuples:

from Puchases p 
where (p.PRODUCT_NAME, p1.PURCHASER_NAME, p1.PURCHASE_DATE) in
    (select PRODUCT_NAME, PURCHASER_NAME , max(PURCHASE_DATE) 
     from Purchases 
     where 
        PRODUCT_NAME in :productNames and 
        PURCHASER_NAME in :purchaserNames 
     group by PRODUCT_NAME, PURCHASER_NAME)

It should be possible to implement the same using criteria API as well, using Subqueries.propertiesIn.

See Hibernate Criteria Query for multiple columns with IN clause and a subselect

If your PURCHASE_ID's are guaranteed to be 'chronologically ascending', then you can simply use max(PURCHASE_ID) in subselect.