prisma1
prisma1 copied to clipboard
prisma
Group By and Aggregated Values
#70 Was a wide ranging discussion of how to support GroupBy and Aggregations in a type safe GraphQL API. This issue takes the learnings from previous discussions and provides a final API Proposal.
Throughout this proposal the examples will be based on this data schema:
type User {
id: ID! @unique
name: String!
age: Int!
salaryBracket: String!
city: String!
}
Note: According to #353 we will introduce a new API version that combines the capabilities of the Simple and Relay API. The API is not final yet, but there will be a relay-style connection field for all relations, providing us a convenient place to introduce aggregation fields.
Retrieving all users who live in Aarhus:
{
allUsersConnection(where: {city: "Aarhus"}) {
edges {
node { id, name }
}
}
}
See example return value
Data:
[
{id: "1", name: "Søren", age: 23, salaryBracket: "0-5", city: "Aarhus"},
{id: "2", name: "Tim", age: 43, salaryBracket: "50-80", city: "Aarhus"},
{id: "3", name: "Nilan", age: 99, salaryBracket: "0-5", city: "Magdeburg"}
]
Return value:
{
allUsersConnection {
edges: [
{ node: { id: "1", name: "Søren" } },
{ node: { id: "2", name: "Karl" } }
]
}
}
Aggregations
Aggregate functions
- avg
- median
- max
- min
- count
- sum
API
Getting the average age of people living in Aarhus is accomplished like this in SQL:
SELECT AVG(age) FROM User WHERE city = 'Aarhus'
With Prisma it would look like this:
{
allUsersConnection(where: {city: "Aarhus"}) {
aggregate {
avg {
age
}
}
}
}
See example return value
Data:
[
{id: "1", name: "Søren", age: 23, salaryBracket: "0-5", city: "Aarhus"},
{id: "2", name: "Tim", age: 43, salaryBracket: "50-80", city: "Aarhus"},
{id: "3", name: "Nilan", age: 99, salaryBracket: "0-5", city: "Magdeburg"}
]
Return value:
{
allUsersConnection: {
aggregate: {
avg: {
age: 33
}
}
}
}
Limiting the scope of aggregations
The normal where, skip, first and orderBy arguments can be used to limit the scope of data included in the aggregations:
{
allUsersConnection(where: {city: "Aarhus"}, first: 5, orderBy AGE_DESC) {
aggregate {
avg {
age
}
}
}
}
This will return the average age of the 5 oldest people in Aarhus
See example return value
Data:
[
{id: "1", name: "Søren", age: 99, salaryBracket: "0-5", city: "Aarhus"},
{id: "2", name: "Tim", age: 99, salaryBracket: "50-80", city: "Aarhus"},
{id: "3", name: "Nilan", age: 99, salaryBracket: "0-5", city: "Aarhus"},
{id: "4", name: "Johannes", age: 99, salaryBracket: "0-5", city: "Aarhus"},
{id: "5", name: "Mathias", age: 99, salaryBracket: "50-80", city: "Aarhus"},
{id: "6", name: "Marcus", age: 5, salaryBracket: "0-5", city: "Aarhus"}
]
Return value:
{
allUsersConnection: {
aggregate: {
avg: {
age: 99
}
}
}
}
Larger example
combining aggregations and data retrieval:
{
allUsersConnection(where: {city: "Aarhus"}) {
aggregate {
avg {
age
}
max {
age
}
}
edges {
node { name, age }
}
}
}
See example return value
Data:
[
{id: "1", name: "Søren", age: 23, salaryBracket: "0-5", city: "Aarhus"},
{id: "2", name: "Tim", age: 43, salaryBracket: "50-80", city: "Aarhus"},
{id: "3", name: "Nilan", age: 99, salaryBracket: "0-5", city: "Magdeburg"}
]
Return value:
{
allUsersConnection {
aggregate: {
avg: {
age: 33
}
max: {
age: 43
}
}
edges: [
{ node: { name: "Søren", age: 23 } },
{ node: { name: "Tim", age: 43 } }
]
}
}
Group
In relational databases, GROUP BY is most often used together with aggregation functions like this SELECT city, AVG(age) FROM User GROUP BY city
Because GraphQL returns tree structured data, it is quite compelling to use groupBy without aggregation functions:
{
allUsersConnection {
groupBy {
city {
key
connection {
edges {
node { id, name }
}
}
}
}
}
}
See example return value
Data:
[
{id: "1", name: "Søren", age: 23, salaryBracket: "0-5", city: "Aarhus"},
{id: "2", name: "Tim", age: 43, salaryBracket: "50-80", city: "Aarhus"},
{id: "3", name: "Nilan", age: 99, salaryBracket: "0-5", city: "Magdeburg"}
]
Return value:
{
allUsersConnection: {
groupBy: {
city: [
{
key: "Aarhus"
connection: {
edges: [
{ node: { id: "1", name: "Søren" } },
{ node: { id: "2", name: "Tim" } }
]
}
},
{
key: "Magdeburg"
connection: {
edges: [
{ node: { id: "3", name: "Nilan" } }
]
}
}
]
}
}
}
Or even in multiple levels:
{
allUsersConnection {
groupBy {
city {
key
connection {
groupBy {
salaryBracket {
key
connection {
edges {
node { id, name }
}
}
}
}
}
}
}
}
}
See example return value
Data:
[
{id: "1", name: "Søren", age: 23, salaryBracket: "0-5", city: "Aarhus"},
{id: "2", name: "Tim", age: 43, salaryBracket: "50-80", city: "Aarhus"},
{id: "3", name: "Nilan", age: 99, salaryBracket: "0-5", city: "Magdeburg"},
{id: "4", name: "Dom", age: 99, salaryBracket: "50-80", city: "Aarhus"}
]
Return value:
{
allUsersConnection: {
groupBy: {
city: [
{
key: "Aarhus"
connection: {
groupBy: {
salaryBracket: [
{
key: "0-5"
connection: {
edges: [
{ node: { id: "1", name: "Søren" } }
]
}
},
{
key: "50-80"
connection: {
edges: [
{ node: { id: "2", name: "Tim" } },
{ node: { id: "4", name: "Dom" } }
]
}
]
}
}
}
},
{
key: "Magdeburg"
connection: {
groupBy: {
salaryBracket: [
{
key: "0-5"
connection: {
edges: [
{ node: { id: "3", name: "Nilan" } }
]
}
}
]
}
}
}
]
}
}
}
Combining groupBy and aggregations
The following query will group by city, return first 5 Users, average age of first 5 users and average age of everyone in city
{
allUsersConnection {
groupBy {
city {
key
firstTwo: connection(first: 2, orderBy: AGE_DESC) {
edges {
node { name }
}
aggregate {
avg {
age
}
}
}
allInCity: connection {
aggregate {
avg {
age
}
}
}
}
}
}
}
See example return value
Data:
[
{id: "1", name: "Emanuel", age: 11, salaryBracket: "0-5", city: "Aarhus"},
{id: "2", name: "Søren", age: 23, salaryBracket: "0-5", city: "Aarhus"},
{id: "3", name: "Tim", age: 43, salaryBracket: "50-80", city: "Aarhus"},
{id: "4", name: "Nilan", age: 99, salaryBracket: "0-5", city: "Magdeburg"}
]
Return value:
{
allUsersConnection: {
groupBy {
city: [
{
key: "Aarhus"
firstTwo: {
edges: [
{ node: { name: "Tim" } },
{ node: { name: "Søren" } }
]
aggregate: {
avg: {
age: 33
}
}
}
allInCity: connection {
aggregate: {
avg: {
age: 25.666
}
}
}
},
{
key: "Magdeburg"
firstTwo: {
edges: [
{ node: { name: "Nilan" } },
{ node: { name: "Søren" } }
]
aggregate: {
avg: {
age: 99
}
}
}
allInCity: connection {
aggregate: {
avg: {
age: 99
}
}
}
}
]
}
}
}
Limitations
Both groupBy and aggregations are on single fields only. You can filter the data that goes into the aggregation, but there is no way to use expressions as keys in a group by query.
Hello Soren, currently contemplating over your proposal. Could you please add the underlying schema as well? It's probably trivial, but I would like to rule out mistakes on my end.
For the multiple level group, can you please add example data (ungrouped as well as grouped)? I can't quite grasp the concept of multi-level groups.
@ejoebstl I have added example responses to all queries. This should make the proposed dynamics very clear :-) Looking forward to your feedback.
The multi level groups are really very simple. By exploiting the fact that we have a wonderful tree structure to place data into. The more interesting question is wether this is useful or not.
It's an excellent idea to allow grouping without aggregation by exploiting the three structure. That's a main limitation of SQL.
The feature itself is very useful. Until now, when you wanted to group data, you needed to come up with either a relation or do it in your application. Grouping and aggregation is not only incredibly useful for building powerful frontends (think of a search feature for thousands of nodes, where you can filter by fields), but also decreases overhead in the backend by a lot. Even if I just want to gather some statistics about my data using the playground, this makes everything easier.
Some considerations:
- Right now it's not possible to use a combination of multiple fields in a groupBy, correct?
- Is it possible to use an aggregation inside a filter? Use case for your example: select all users with more than medium age.
- I'd suggest to also add a count_distinct aggregation to count all distinct values of a field.
- Will this proposal also work for the Simple API, or is the Simple API a thing of the past anyway?
I'm quite sure the proposal is a good way though. The few points above can most likely be added afterwards without any complication.
- Right now it's not possible to use a combination of multiple fields in a groupBy, correct?
Correct. It's also not possible to use an arbitrary expression. I think this ability might be worth giving up in trade for a simple type-safe API
- Is it possible to use an aggregation inside a filter? Use case for your example: select all users with more than medium age.
See proposal #1279
- I'd suggest to also add a count_distinct aggregation to count all distinct values of a field.
Great idea!
- Will this proposal also work for the Simple API, or is the Simple API a thing of the past anyway?
In the future there will be only a single API flavour as described in #353
There is no example for a count aggregation, I'm guessing it looks like this:
{
postsConnection {
aggregate {
count
}
}
}
Please confirm or correct!
Is it possible to order by aggregated value? I try to do a something like:
Course
-- Episodes
---- Views
Views model
{
date: DateTime! @unique
views: Int!
}
I want to query top Course order by daily / weekly / ... views. It will sum all episiodes views between 2 date and order by that sum.
Why was this issue moved to the graphcool-framework repo?
I thought that Group By and Aggregated Values would be implemented in Prisma.
The Prisma documentation links to this issue
@jvbianchi
As I know Graphcool Framework is a GraphQL backend solution. Still a lot of people using it like me.
Prisma is not a replacement. It is an open-source GraphQL query engine can connect to a lot of different database not just Graphcool Framework. It's a standalone version of Graphcool 1.0 and they will go a different way from now.
You can read it here: https://www.graph.cool/forum/t/graphcool-framework-and-prisma/2237
I'm still waiting for them to this features, because I think I'll stick with Graphcool Framework. :)
Everyone can correct me if I'm wrong.
@kieusonlam Ok, but that doesn't explain why this feature will not be implemented in Prisma as well.
the count aggregate function has already been implemented, why not the others too?
@jvbianchi It's already have this feature. You can check the example here: https://github.com/graphcool/graphql-server-example
topHomes query have numRatings which is defined in
https://github.com/graphcool/graphql-server-example/blob/master/src/resolvers/Home.ts
@kieusonlam That is what I just said. count has been implemented.
But avg, median, max, min, sum and group by have not.
Do you have a example with any of this other aggregated functions?
@jvbianchi Hmm, yup, that's my bad. It's still missing avg, median, max, min, sum. We may wait for graphcool team to have the right answer.
My bad 🙂 I'll open this issue again, thanks for the heads up @jvbianchi.
@kieusonlam, the plan is to eventually bring back the evolution of the Prisma API into the Graphcool Framework.
Just to confirm we cannot currently access these query filter options or fields right. I can’t seem to find connections or use the where clause in the playground which means it is impossible to do this sort of complex query on counts of edges for example, right?
Might be completely off topic here, but you might want to look at how OData has implemented aggregation as it's rather flexible and covers a lot of complex use cases. You can read the specification here.
I've also written a JSON query object to generate the OData query string as it can be rather difficult to built it out using just string building (especially with nesting). This might be useful as inspiration for how GraphQL might support this - https://github.com/techniq/odata-query#transforms
One of the more complex patterns is applying a filter before aggregation and another after. I have an example of this in my README (you can also look at the various tests of the project as well).
Is there any progress on this? Could the ability to perform raw sql queries using prisma be added to overcome waiting for features like these be implemented? I think there's always gonna be an edge case where the CRUD api falls short, and it would be good to have a scape hatch for those cases, guaranteeing that the decision of using prisma scales to a complex project
Sure, that's a great point. You are connecting your database to Prisma, so you can also send raw queries there 🙂
@danielrasmuson we are currently putting together a public roadmap for the next 6-12 months. It is safe to say that this feature will be on the roadmap as it is very highly requested :-)
Looking forward to this as we're currently in need of this and have run into this limitation multiple times with both graphcool and prisma over the last few months. Let me know if there's anything I can help with this @sorenbs
@marktani, where can I learn more about this statement?:
Sure, that's a great point. You are connecting your database to Prisma, so you can also send raw queries there 🙂
Going to bump as well. Not having this feature == lots more work and poor client performance. :)
Will it be possible to use aggregates in filter query? For example, to get active users by number of commits they made:
query activeUsers {
users(where: {
commits: {
date_gte: "THIS_MONTH_DATE",
aggregate: {
count_gte: 5
}
}
}) {
email
}
}
@sorenbs @schickling This feature is planned for Q3 in 2018. Only 1 month till the end of Q3. Any progress? Will aggregate functions be implemented at once or one by one? I really need avg for my project.
