Order by aggregates with filters about graphql-engine HOT 1 OPEN

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.

from graphql-engine.