Order by related field

[xpt1x]

Is it possible to order by a related field (which is an array)'s first element's property?
For ex:
People "people" is base model
And has property addresses [] where each address is an object with details like

{ city: str, country: str }

Now i want to query all people with ordering like this: GET / people?order=addresses->0->city
Note: I want to order people not addresses

I am not using latest stable as it doesnt support ordering by related fields but instead using postgrest:v10.2.0.20230209

[laurenceisla]

Yes it is possible. I'm assuming your addresses column is json[], right? Then doing order=addresses->0->city or order=addresses->0->>city should work.

I am not using latest stable as it doesnt support ordering by related fields but instead using: postgrest:v10.2.0.20230209

Are you sure about this? I think this is fully supported since v10.0.0. If it does not work for you, do you have a reproducible example to see what's the issue?

[xpt1x]

No, Address is not json[], it has its own model/structure like a usual sql scenario.
So there exists a different table for "address"

Yes i am sure, i am getting this similar error #2702

[laurenceisla]

So there exists a different table for "address"

Ah, I misunderstood. You're right, there isn't a stable release with that change yet, it will be available on the next version. It's available in the pre-release you mentioned for now. The changelog gives a simple example on how to use the feature:

postgrest/CHANGELOG.md

Lines 10 to 11 in caaa9a3

	- #1414, Add related orders - @steve-chavez
	+ On a many-to-one or one-to-one relationship, you can order a parent by a child column `/projects?select=*,clients(*)&order=clients(name).desc.nullsfirst`

But, still, for your case I don't think it's going to be possible, because the relationship between people and addresses is of one-to-many, which is not supported. Perhaps you need to create a VIEW with the JOIN just for this case or add a new Computed Relationship, to force a many-to-one scenario, something like:

create function api.main_address(api.people) returns setof api.addresses rows 1 as $$
  select * from api.addresses where people_id = $1.id limit 1
$$ stable language sql;

Then the embedding / ordering would be:

GET /people?select=*,main_address(*)&order=main_address(city)

[xpt1x]

Thanks for clarifying