goent

Nested aggregation in Ent Query


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.


Solution

  • 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.