Diesel: Query most recent row of data, per group

Question

A common SQL query is to find the "most recent row, per group". For example, the following query returns the most recent balance per user:

SELECT user, date, balance
FROM balances t1
INNER JOIN (
    SELECT user, max(date) as most_recent_date
    FROM balances
    GROUP BY user
) t2
    ON t1.user=t2.user AND t1.date=t2.most_recent_date

How would we do this with the Diesel (version 2) ORM? It doesn't have to be a literal translation of the SQL query, just needs to functionally do the same thing. I've tried creating the equivalent of a "subquery":

let latest_row_per_group = balances::table
    .group_by(balances::user)
    .select((
        balances::user,
        diesel::dsl::max(balances::date),
    ));

but I'm not able to join this to the original table, because it's a SelectStatement type, not actually a table.

Would love to avoid writing raw SQL here if possible.

Answer 1

Diesel's built-in DSL does currently not support joins to subqueries. You can extend the DSL in a third party crate. The Extending Diesel guide contains an example for that, although for a slightly different query.