Support GROUP BY

[darsana]

I want the result for the bellow query,Can u help?

SELECT * FROM account_details WHERE UserId = '1' AND PartyRelationshipTypeId IN ( 5,6 ) GROUP BY PartyRoleID1 ORDER BY OrganizationName ASC

[begriffs]

It's not yet able to do all those things. The closest you can currently do is request this:

/account_details?UserId=eq.1&order=OrganizationName.asc

The "select...in" is an upcoming feature described in issue #98. Also group by is not supported, but I'll add it to my todos.

I'll work in implementing as much as I can this weekend.

[darsana]

thank you for the update, We are waiting for the implementation...

[begriffs]

Actually I just realized you can get much of this functionality by creating a sql view.

SELECT * FROM account_details
GROUP BY PartyRoleID1 ORDER BY OrganizationName ASC

Name it something like account_details_by_org. It will be accessible just like a table. Then access it like

/account_details_by_org?UserId=eq.1&PartyRelationshipTypeId=in.5,6

The in keyword won't work yet, but all the rest will. Like I said I'll work on implementing the in thing over the weekend.

[razakpm]

@begriffs Thanks for the update , I'm also eagerly waiting for the IN concept. Will check the Group By and Order By concepts and will get back to you.

[darsana]

@begriffs Hope your working on the IN implementation in postgrest..we are eagerly waiting for the same

[begriffs]

I have implemented the IN feature and it's on master now. Just adding one other feature and I'll release a new version and new binaries.

[darsana]

@begriffs Thanks for your update..waiting for the new release .

[arunr307]

Thanks for your update and new binary. Is give a solution for "IN' query. Thanks..

[begriffs]

@razakpm any luck getting group by / order by to work the way you need?

[begriffs]

I'm closing this issue, but we can open it again if there is anything else you need to make your group by view work.

[sscarduzio]

I get it that you can use a view, but what's the obstacle to enriching the API with a group by? Is there some?

[ruslantalpa]

How eould the url look like?

[ruslantalpa]

Would*

[sscarduzio]

Well for example:

GET /event_feedbacks?select=rating,count(*)&eid=not.is.null&groupby=rating
[{ "rating": 1, "count":3}, { "rating": 2, "count":8}]

SELECT rating, count(*) FROM event_feedbacks WHERE eid IS NOT NULL GROUP BY rating;

[ruslantalpa]

The point is not expose the whole sql. Postgrest always generates a query with the same predictable shape so it's easy to optimise for it, allowing functions (like count or things like that) will allow web users to ddos any deployment with ease.

[sscarduzio]

Wait, now I'm a bit confused, if you remember we discussed about another feature few days ago and you said it's better to delegate this kind of security checks, rate limiting and other ordinary security concerns to nginx.

Don't get me wrong, I see the point of group by being more intensive/dangerous query to be exposed directly, but I still feel this concern belongs to the same category of issues that you planned to resolve with putting an nginx in front of PostgREST: e.g. barring "groupby" as a query parameter when exposing PostgREST directly to the clients.

[ruslantalpa]

ratelimiting is one thing able to generate a single query that can kill the server is a completely different thing, and you don't want to have a lot of configuration in nginx, just basic stuff

[sscarduzio]

so if my logic implies group by style aggregations it's better downloading all the table in the client and crunching the numbers locally?
This might be very unpractical, what do you suggest?

[ruslantalpa]

i am not suggesting that, you create a view/rpc for that

[Sigura]

same suggestion:

#167 (comment)

[jdgamble555]

ratelimiting is one thing able to generate a single query that can kill the server is a completely different thing, and you don't want to have a lot of configuration in nginx, just basic stuff

This is a ridiculous argument not to implement a needed feature. The database is susceptible to ddos attacks anyway as far as the limits of your infrastructure, and there are already count algorithms available on the client.

Let's add this feature please so this package can be usable without creating dozens of Views for basic use cases!

J

[mysterious-ben]

It's been quite a while since there was any update on this issue, so I wonder: is adding GROUP BY to the Postgrest API still considered dangerous?

To me, this seems to be a rather forced argument to justify not adding this very useful feature. The alternative of creating dozens of aggregated views is certainly less convenient. Regarding security: maybe it would be possible to add a config flag allowing or forbidding GROUP BY clause in the API if this is deemed to be critical?

[wolfgangwalther]

Instead of digging up old, closed issues, you might look into:

• release notes: https://github.com/PostgREST/postgrest/releases/tag/v12.0.0
• docs: https://postgrest.org/en/v12/references/api/aggregate_functions.html
• more recent issues about this: Ability to generate queries with distinct/group by #915