Sort By Count Aggregation

[jdgamble555]

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

#158
#167
#915
#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

[arbela-cyberillium]

I would love to just be able to get the count

?select=title,directors(_aggregate.count)

so that way I can know how many directors are there for the film (I didn't found any work around that will support filters )

[steve-chavez]

?select=title,directors(_aggregate.count)

Ah, actually the above already works like:

GET /tbl?select=title,directors(count)

Caveats:

• You can't include any other columns inside directors(..).
• The count will be inside an array.

[steve-chavez]

To elaborate on the above:

curl 'localhost:3000/clients?select=id,name,projects(count)'

[
 {"id":1,"name":"Microsoft","projects":[{"count":3}]},
 {"id":2,"name":"Apple","projects":[{"count":2}]}
]

---

Addressing the OP issue.

Using the new related order feature: #2511

I tried to do:

curl 'localhost:3000/clients?select=id,name,projects(count)&order=projects(count)'

{"code":"PGRST118","details":"'clients' and 'projects' do not form a many-to-one or one-to-one relationship","hint":null,"message":"A related order on 'projects' is not possible"}

It's close.. the underlying query should work. However, we need a way to mark the projects(count) being a single row.

[jdgamble555]

@steve-chavez - that would be awesome if ya'll can figure that out! Simple, short, would solve a lot of groupby use cases too!

J

[hcharley]

Hi, I'm new to this community and to PostgREST, so apologies if this isn't the appropriate way to express this:

I'd love to use an order for a sum aggregation, so I just wanted to chime in to let contributors to this project that there is additional interest in seeing this ticket worked on.

My use case is kind of like this:

GET /person?select=id,name,donations:donation!inner(count(), total_donated:amount.sum())?order=donations:donation!inner(amount.sum())

[therealsujitk]

Would be awesome if this were possible! I don't suppose there's a current workaround that won't require me having to create a view or a computed column?

---

Alright, I figured out one workaround for if you have a one-to-many relationship. All I did was look at it from the other end and try and make it a many-to-one relationship.

To elaborate on the above:

curl 'localhost:3000/clients?select=id,name,projects(count)'

[
 {"id":1,"name":"Microsoft","projects":[{"count":3}]},
 {"id":2,"name":"Apple","projects":[{"count":2}]}
]

So I guess the above request would become something like this.

curl 'localhost:3000/projects?select=count(),...clients!inner(id,name)'

@steve-chavez can you confirm this? Seems like a good workaround for me, but won't work for many-to-many relationships.

[laurenceisla]

Alright, I figured out one workaround for if you have a one-to-many relationship. All I did was look at it from the other end and try and make it a many-to-one relationship.

So I guess the above request would become something like this.

curl 'localhost:3000/projects?select=count(),...clients!inner(id,name)'

@steve-chavez can you confirm this? Seems like a good workaround for me

Ah, that's an interesting workaround. You can get the count without wrapping it in arrays and add an order like &order=count.desc. The drawback is that this will only work for !inner joins, for example, the clients without projects won't show in the result.

but won't work for many-to-many relationships.

I think It could work for many-to-many if you used the intermediate table instead:

# "users_projects" is junction table between "projects" and "users"
curl 'localhost:3000/users_projects?select=count(),...projects!inner(name)'

[{"count":1,"name":"OSX"},
 {"count":2,"name":"IOS"},
 {"count":2,"name":"Windows 7"},
 {"count":1,"name":"Windows 10"}]

[therealsujitk]

The drawback is that this will only work for !inner joins, for example, the clients without projects won't show in the result.

Yeah, you're right. I just ran into that and realised it too, sadly PostgREST doesn't support other types of joins yet (I think RIGHT is what I'd need in my case).

I think It could work for many-to-many if you used the intermediate table instead

True, if you create an intermediate view a lot of our problems would be solved, but I actually personally prefer not to create new views unless I have a good reason to. I prefer to just write a complex query and not add anything new to the database.