graphql-engine
graphql-engine copied to clipboard
hasura
Feature request: Support aggregate(count, max, min...) in where clause
Currently, there's no support for aggregate fields in where and order_by clause, which means it's impossible to do filtering on aggregation.
Suggestion: Support aggregated fields on where and order_by clause when we query from a table_aggregate.
Example:
query {
users_aggregate {
aggregate(where: {count: {_gt: 0} })
{
count
}
}
}
or
query {
users_aggregate(where: {aggregate {count: {_gt: 0} }}) {
aggregate
{
count
}
}
}
Any news on this? This seems like a big deal, and not terribly hard to translate to sql under the hood:
SELECT COUNT(*)
FROM blah
WHERE foo = bar
Adding a voice of support here. You can already do aggregate queries with a where clause inside of the object.
investigation {
investigation_history_aggregate(where: {interaction: {_eq: "added_note"}}) {
aggregate {
max {
created_on
}
}
}
}
Found it weird that you couldn't do that in the order/where clause.
Use case:
Fetch a list of authors (who have published more than 10 articles ever) and their articles (published between dates a and b). The list of authors is to be sorted based on the count of articles published between dates a and b
Really need this, for instance if I'm doing a search and I still want to paginate the results I need to return the count of the searched items, not the whole table.
+1 again from me.
It's already possible to do very complex queries and filtering with awesome results. But as soon as it comes to sorting the results it is impossible. In many cases there is no good workaround and you need to bypass Hasura.
@rikinsk @shahidhk @rakeshkky @wawhal @arvi3411301
Can we please pick this up next? There seems to be huge demand for this 🚀
Does anybody know any temporary hacks to get this done? I really don't want to stink up my project with raw SQL scattered about (which hasura was supposed to solve completely 😋)
Currently I am using this kind of work around for aggregation inside of query. Hope it helps.
query statistics {
FindOutFrom(where:{ Patients: {id: { _is_null: false }}}) {
name,
Patients_aggregate {
aggregate {
count
}
}
}
}
Echoing this! Would be super useful. Have run into needing this several times now.
+1 Looking to be able to achieve multiple counts based off of different where clauses in a single aggregation query. The following workaround is terribly non performant.
query MyQuery($input_start_date: date, $input_end_date: date) {
count1: keyword(args: {input_end_date: $input_end_date, input_start_date: $input_start_date}, where: {bid_diff: {_gt: "0"}}) {
aggregate {
count
}
}
count2: keyword(args: {input_end_date: $input_end_date, input_start_date: $input_start_date}, where: {bid_diff: {_lt: "0"}}) {
aggregate {
count
}
}
count3: keyword(args: {input_end_date: $input_end_date, input_start_date: $input_start_date}) {
aggregate {
count
avg {
recommended_bid
bid_diff
}
}
}
}
Would be nice if in the query itself you could add *count to the end of where clauses or at the end of a query to get aggregation totals.
keyword(args: {input_end_date: $input_end_date, input_start_date: $input_start_date}, where: {bid_diff: {_lt: "0"} *count: bid_diff_count }) {
data
*count // as total aggregate count
*bid_diff_count
}
Ended up having to use a view with aggregations, and then setting up a relation.
Ideally we get proper support.
+1, would be a massive improvement to server side pagination with filters on aggregate fields
What's the standing on this issue @Hasura? The feature request is widely wanted. Would love to use it, thanks.
Hello @revskill10 or anyone interested in this feature!
For further clarification of the request would this query also be an example of what you're looking for?
author (where: {articles_aggregate: {count: {_gt: 5}}) { id name }
Plus if there ae any other additional user story level example that would be very helpful.
One clarifying question: Are you considering querying the aggregate AND what is inside the aggregate?
Thanks!
@dennislaviolette Yes, it's one of the query that i want.
The goal, is to build a search interface which shows facet statistics, for example, in an ecommerce, in search result, we want to show categories (count) of the search result.
You can take a look at amazon search result and its facet search on the left sidebar as a reference.
Thanks !