Sort By Aggregation

While there is a need for GROUP BY in PostgREST, this feature would take care a lot of the use cases:

https://github.com/PostgREST/postgrest/issues/158 https://github.com/PostgREST/postgrest/issues/167 https://github.com/PostgREST/postgrest/issues/915 https://github.com/PostgREST/postgrest/issues/158

It would be nice to be able to say:

GET /films?select=title,directors(id,last_name,_aggregate.count)?order=last_name(_aggregate.count) HTTP/1.1

I'm thinking you should be able to sort by the top level table OR by the foreign key / child table.

It should work similar to GraphQL like Hasura:

https://hasura.io/docs/latest/queries/postgres/sorting/#for-array-relationships

That way, we don't need to think about Group By, but think about the actual data and how you want to sort it.

So, from a regular data perspective, let's say we want to display the Top Votes of a Post:

CREATE TABLE posts (
  id uuid
  vote_id uuid
  ...
)
CREATE TABLE votes (
  post_id uuid REFERENCES posts(vote_id)
  user_id uuid REFERNCES users(id)
  PRIMARY KEY(post_id, user_id)
  ...
)

So we want something like this:

?order=posts(votes._count)

OR

?order=posts(votes._aggregate.count)

There are different ways to accomplish this, but basically you wouldn't need to think about GROUP BY, and it would write this SQL code automatically (maybe with Group By).

J