Subquery for finding the latest record for a group.

[buck...@gmail.com]

I'd love to hear suggestion on how to do this in ebean:

Given a table transactions with columns: customer, store, transact_date, product, quantity, price

I'd like to find the latest transaction for each customer in a list of stores.   The sql can be written as

SELECT a.* FROM  transactions a

WHERE NOT EXISTS (SELECT 1 FROM transactions b WHERE b.customer=a.customer AND b.store=a.store AND b.transat_date>a.transact_date) and a.store in (?, ?, ?, ?)

My question is where to put the subquery for notExist and how to join to itself.  Thanks for your help.

Buck

[Ojasv Singh]

Creating the exact query you've given using Ebean's ORM features can be difficult due to its limitations with correlated subqueries. I solved such issue by using rawsql. Would love to hear if someone has a better solution. 

[Rob Bygrave]

So ebean will use t0 as the table alias for the root table so you'd do:

query.where()

.notExists("SELECT 1 FROM transactions b WHERE b.customer = t0.customer AND b.store= t0.store AND b.transat_date > t0.transact_date")
.in("store", List.of(1,2,3,4))

.findList()

 If we want to specify a different table alias for the root table like say "a" then we can do that via query.alias("a")

 Did you do it this way or something else?

Cheers, Rob.

[buck...@gmail.com]

> Did you do it this way or something else?

I tried NativeSql first, but that didn't allow me to use ExpressionList for specifying predicates.  I ended up creating a view for the query below and create an entity to use the view (this was not my preferred option as I had to map the "view" entity to the "table" entity).

SELECT a.* FROM  transactions a

WHERE NOT EXISTS (SELECT 1 FROM transactions b WHERE b.customer=a.customer AND b.store=a.store AND b.transat_date>a.transact_date)

> So ebean will use t0 as the table alias for the root table so you'd do:

I'd definitely give it a try.    Thanks for the suggestion.

Buck