Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Group By and Aggregated Values #1312

Open
sorenbs opened this Issue Nov 21, 2017 · 39 comments

Comments

Projects
None yet
@sorenbs
Copy link
Member

sorenbs commented Nov 21, 2017

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

@sorenbs sorenbs added the rfc/1-draft label Nov 21, 2017

@sorenbs sorenbs self-assigned this Nov 21, 2017

@nikolasburk nikolasburk referenced this issue Nov 21, 2017

Open

[Prisma 1.0] Specifications #353

28 of 32 tasks complete

@sorenbs sorenbs referenced this issue Nov 21, 2017

Open

Support filtering on aggregated values #1279

0 of 1 task complete
@ejoebstl

This comment has been minimized.

Copy link
Collaborator

ejoebstl commented Nov 21, 2017

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.

@ejoebstl

This comment has been minimized.

Copy link
Collaborator

ejoebstl commented Nov 21, 2017

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.

@sorenbs

This comment has been minimized.

Copy link
Member Author

sorenbs commented Nov 21, 2017

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

@ejoebstl

This comment has been minimized.

Copy link
Collaborator

ejoebstl commented Nov 22, 2017

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:

  1. Right now it's not possible to use a combination of multiple fields in a groupBy, correct?
  2. Is it possible to use an aggregation inside a filter? Use case for your example: select all users with more than medium age.
  3. I'd suggest to also add a count_distinct aggregation to count all distinct values of a field.
  4. 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.

@sorenbs

This comment has been minimized.

Copy link
Member Author

sorenbs commented Nov 22, 2017

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

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

  1. I'd suggest to also add a count_distinct aggregation to count all distinct values of a field.

Great idea!

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

@schickling schickling referenced this issue Nov 30, 2017

Closed

Prisma: Generated type names #1341

0 of 3 tasks complete

@sorenbs sorenbs added rfc/2-accepted and removed rfc/1-draft labels Dec 1, 2017

@sorenbs sorenbs added rfc/1-draft and removed rfc/2-accepted labels Dec 9, 2017

@nikolasburk

This comment has been minimized.

Copy link
Member

nikolasburk commented Dec 11, 2017

There is no example for a count aggregation, I'm guessing it looks like this:

{
  postsConnection {
    aggregate {
      count
    }
  }
}

Please confirm or correct!

@mavilein mavilein referenced this issue Dec 14, 2017

Merged

[WIP] Prisma #1318

41 of 42 tasks complete

@marktani marktani added this to the 1.0-beta3 milestone Dec 15, 2017

@kieusonlam

This comment has been minimized.

Copy link

kieusonlam commented Dec 16, 2017

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.

@sorenbs sorenbs modified the milestones: 1.0-beta3, 1.1 Jan 1, 2018

@marktani marktani removed this from the 1.1 milestone Jan 22, 2018

@marktani marktani closed this Jan 23, 2018

@jvbianchi

This comment has been minimized.

Copy link

jvbianchi commented Jan 26, 2018

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

@kieusonlam

This comment has been minimized.

Copy link

kieusonlam commented Jan 26, 2018

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

@jvbianchi

This comment has been minimized.

Copy link

jvbianchi commented Jan 26, 2018

@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?

@kieusonlam

This comment has been minimized.

Copy link

kieusonlam commented Jan 26, 2018

@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

@jvbianchi

This comment has been minimized.

Copy link

jvbianchi commented Jan 26, 2018

@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?

@kieusonlam

This comment has been minimized.

Copy link

kieusonlam commented Jan 26, 2018

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

@techniq

This comment has been minimized.

Copy link

techniq commented Jan 28, 2018

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

@marcosfede

This comment has been minimized.

Copy link

marcosfede commented Feb 26, 2018

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

@marktani

This comment has been minimized.

Copy link
Contributor

marktani commented Mar 3, 2018

Sure, that's a great point. You are connecting your database to Prisma, so you can also send raw queries there 🙂

@danielrasmuson

This comment has been minimized.

Copy link

danielrasmuson commented Mar 19, 2018

Is this proposal on the roadmap?

@sorenbs

This comment has been minimized.

Copy link
Member Author

sorenbs commented Mar 20, 2018

@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 :-)

@Nedomas

This comment has been minimized.

Copy link

Nedomas commented Mar 28, 2018

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

@MJones180

This comment has been minimized.

Copy link

MJones180 commented May 31, 2018

Any idea where this is on the roadmap? Highly needed 👍

@sakhmedbayev

This comment has been minimized.

Copy link

sakhmedbayev commented Jun 5, 2018

@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 🙂

@arnabkd

This comment has been minimized.

Copy link

arnabkd commented Jun 22, 2018

shameless bump: begging for this feature ;)

@oae

This comment has been minimized.

Copy link

oae commented Jul 24, 2018

Any update for this feature?

@gentle-noah

This comment has been minimized.

Copy link

gentle-noah commented Jul 26, 2018

Going to bump as well. Not having this feature == lots more work and poor client performance. :)

@kirgene

This comment has been minimized.

Copy link

kirgene commented Aug 25, 2018

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
   }
}
@FluorescentHallucinogen

This comment has been minimized.

Copy link

FluorescentHallucinogen commented Sep 1, 2018

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

@FluorescentHallucinogen

This comment has been minimized.

Copy link

FluorescentHallucinogen commented Oct 16, 2018

Q3 2018 is over. Any news?

@ellipticaldoor

This comment has been minimized.

Copy link

ellipticaldoor commented Oct 23, 2018

I need max, there is any way I can get this functionality?

@kevinmarrec

This comment has been minimized.

Copy link

kevinmarrec commented Oct 30, 2018

@sorenbs Any news on this ? Can the Roadmap label be updated if it's planned for later ?

@MJones180

This comment has been minimized.

Copy link

MJones180 commented Nov 3, 2018

Q3 has been over for a while and still no response as to the current status of this. An update would be nice 👍

@stephen-bunn

This comment has been minimized.

Copy link

stephen-bunn commented Nov 6, 2018

Also looking for an update on the status of this.

@sorenbs

This comment has been minimized.

Copy link
Member Author

sorenbs commented Nov 14, 2018

This continues to be an important feature for us. I'll update this issue when we have a concrete timeframe. See also this explanation for why we were unable to ship this feature in Q3 as planned.

@FluorescentHallucinogen - we will likely implement a large chunk of this feature in one go as each individual aggregation is comparatively little work.

@joshhopkins

This comment has been minimized.

Copy link

joshhopkins commented Jan 25, 2019

Any ETA on this? Very much needed 🙏🏼

@impowski

This comment has been minimized.

Copy link

impowski commented Feb 5, 2019

Waiting for this one to drop, there will be a big use in our project in production!

@cihadturhan

This comment has been minimized.

Copy link

cihadturhan commented Mar 8, 2019

At least implement sum :)

@terion-name

This comment has been minimized.

Copy link

terion-name commented Mar 12, 2019

any eta?(

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.