Order by aggregate count when there are two aliases using where

[kevinwasie]

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"
    }
  }
}

[manasag]

Your findings are correct. Looks like Hasura doesn't support ordering by aggregates with additional filters. Converting this into an enhancement request. #10526