Nested aggregation in Ent Query

Question

How can I write this simple SQL statement using the code generated by Ent?

select max(t.sum_score) from
                             (select sum(score) as "sum_score"
                              from matches
                              group by team) as t

I tried to use Custom SQL Modifiers feature flag as described here but I can't figure out how to access the sum_score field from outside of the modifier.

Answer 1

This is the answer from a8m the owner of the Ent Project (thank you!)

client.Match.Query().
    Aggregate(func(s *sql.Selector) string {
        const as = "max_score"
        s.GroupBy(match.FieldTeam).OrderBy(sql.Desc(as)).Limit(1)
        return sql.As(sql.Sum(match.FieldScore), as)
    }).
    IntX(ctx)

You can find the full answer here on the official GitHub repo. I had to add sql.Desc(as) to get the maximum value.