order_by aggregated fields don't use filter

[rafaelugolini]

Hello,

I'm having an issue while using order_by on aggregate fields.

{
  author (order_by: {articles_aggregate: { count: desc }}) {
    id
    name
    articles_aggregate(where: $where) {
        aggregate {
            count
        }
    }      
  }
}

It looks like the SQL query doesn't apply the filter used in the aggregated count on the order_by count. Am I doing something wrong?

#1039 #1042

[0x777]

@rafaelugolini This is currently the expected behavior. The filters used on *_aggregate fields in the query are not applied at the top level. For example, you can have such a query:

{
  author (order_by: {articles_aggregate: { count: desc }}) {
    id
    name
    count1: articles_aggregate(where: $where1) {
        aggregate {
            count
        }
    }    
    count2: articles_aggregate(where: $where2) {
        aggregate {
            count
        }
    }      
  }
}

In this case, we wouldn't know which filter to apply at the top level.

Your use case however is very valid. Maybe we can extend the order_by syntax to allow specifying the where clause?

{
  author (order_by: {articles_aggregate: { where: $where using: {count: desc} }) {
    id
    name
    articles_aggregate(where: $where) {
        aggregate {
            count
        }
    }      
  }
}

[rafaelugolini]

@0x777 that would work 👍

btw thanks for the awesome response time, I ❤️ hasura

[hoanv810]

hi @rafaelugolini : I'm facing the same issue like yours. How did you solve that?

[rafaelugolini]

@hoanv810 hey :) from what I remember we created a view

[hastarin]

This is definitely a feature we could use. Hopefully once 1.0 is out things like this can get looked at for a future roadmap.

[karibertils]

Any news on this ?

I am working with custom reports where the filters are quite dynamic and it is not possible to solve my case with views.

Having the possibility to add where filter to the order_by clause like @0x777 mentioned would solve this perfectly.

[manuFL]

Hi, any news, ETA on this? Hasura is terrific, but the lacks of this feature (as suggested from @0x777 would be great) breaks all our server side pagination/reorder, thus preventing us from be able to deploy with large datasets...

Thank you in advance

[korengrip]

We need this feature. Anyone solved this? working on it? Thanks in advance

[karibertils]

With all the amazing improvements in hasura recently, I find this to be the only area where hasura is still lacking.

[manuFL]

Hi 0x777, cloud you kindly provide us some more information of hasura willingness to implement a solution like the one you suggested? It is really much need; without, most of the pagination logic is broken, and with large dataset it is an important problem. If there is something we have to pay to have this fix implemented OK, but please let us know... thank you!

[norwindijkman]

@manuFL Couln't you solve your problem with a Postgres function? Just pass the $where variable to that function, and let it handle the return order.

[manuFL]

Hi @norwindijkman, we are are using a lot of postgres functions as a workaround for this specific issue but they are really unhandy... you must implement all the filtering and ordering logic from scratch, and keep it updated each time you need a new filter etc.

Most of our postgres functions wouldn't be necessary with this feature available, letting us use hasura at full power with native queries...

[ananni13]

The solution proposed by @0x777 (https://github.com/hasura/graphql-engine/issues/1357#issuecomment-453004951) seems perfect.

Any updates on this?

[prag-matt-ic]

We could really do with this too

[RodriguezLucha]

Ran into this as well!

[austin-coffman]

Any ETA on this? Would be extremely useful

[akhilrajvc]

Any update on this feature?

[rccoe]

Is this anywhere on the roadmap (3 years later)? This is a crucially missing piece of the puzzle. This would solve it: https://github.com/hasura/graphql-engine/issues/7473

[karibertils]

Since #1498 has been solved, I am wondering if we might be seeing this one implemented as well ?

Really important for my use case.

[RayyanRiaz]

Any updates here? Would be very helpful for my project too. Currently, I have to create two different views just as a workaround for this issue.

[swtmply]

Any updates?

[SamirTalwar]

Hey folks. As a little sneak preview: Native Queries will be out of beta and available in Hasura CE for PostgreSQL with the release of v2.28, which is imminent. Native Queries allow you to express your query as arbitrary SQL, while still allowing you to extend them with filtering, ordering, etc. on the Hasura side.

This means you can work around this issue by writing the query, which might look something like this:

SELECT author.id, author.name, count(article.id) AS article_count
FROM author
JOIN article ON author.id = article.author_id
WHERE <your where clause>
GROUP BY author.id, author.name
ORDER BY count(article.id) DESC

Then you can query as usual with Hasura, and apply successive filters in GraphQL which will not impact the article count.