Order by aggregates with filters

[manasag]

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