Order by aggregates with filters #10526
Description
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"
}
}
}