Order by aggregates with filters

[manasag]

trafficstars

Discussed in https://github.com/hasura/graphql-engine/discussions/10522

^{Originally posted by kevinwasie September 3, 2024} With the below GraphQL, how do I sort the users[] list by the count of 'user_houses' or 'user_jobs'?

I'm using backend pagination and backend fetches for refreshed data on sort requests. The dataset is to large to pass it everytime to the front end to handle this. Is there a way?

query users($limit: Int!, $offset: Int!, $order_by: users_order_by!) {
  users(limit: $limit, offset: $offset, order_by: [$order_by]) {
    id
    full_name
    user_houses: users_to_buildings_aggregate(where: {role: {_eq: "tenant"}}) {
      aggregate {
        count
      }
    }
    user_jobs: users_to_buildings_aggregate(where: {role: {_eq: "administrator"}}) {
      aggregate {
        count
      }
    }
  }

The query produces:

{
  "data": {
    "users": [
      {
        "id": "asdf",
        "full_name": "User 1",
        "user_houses": {
          "aggregate": {
            "count": 2
          }
        },
        "user_jobs": {
          "aggregate": {
            "count": 1
          }
        }
      },
      {
        "id": "asdf",
        "full_name": "User 2",
        "user_houses": {
          "aggregate": {
            "count": 0
          }
        },
        "user_jobs": {
          "aggregate": {
            "count": 4
          }
        }
      }
    ]
  }
}

I've tried the following but neither of them work:

This removes the filters and simply sorts by the total aggregate of users_to_buildings

{
  "limit": 10,
  "offset": 0,
  "order_by": {
    "users_to_buildings_aggregate": {
      "count": "asc"
    }
  }
}

This produces an error

{
  "limit": 10,
  "offset": 0,
  "order_by": {
    "user_houses": {
      "count": "asc"
    }
  }
}

[rakeshkky]

From the GraphQL query POV, both user_houses and user_jobs are aliases backed by the same GraphQL field. However, you cannot use them as arbitrary input fields, as the latter are specified by input object types in GraphQL schema.

For your use-case, I can think of defining views out of buildings table with filters using role column.

create view buildings_tenant as select * from buildings where role = 'tenant'

and

create view buildings_admin as select * from buildings where role = 'administrator'

Track those views and define array relationships from users table.

user_houses: users -> building_tenant
user_jobs: users -> building_admin

You can use the aggregate fields of above relationships in order_by.

[danieljharvey]

Hello! There's been no activity on this issue so hopefully the suggestion above worked. Please feel free to re-open if you have further questions!

[Notalifeform]

Creating a view to enable sorting is just a work around for the limitation that hasura can not sort on filtered aggregates.

Isn't the whole point of hasura to provide a flexible query engine that does not require you to make changes to the backend for each front-end requirement?

please re-open this issue so it kept as a enhancement in the issue-list