Supabase: .order() not working on joined table column
Hi everyone, I have the following tables:
table public.buildings (
id uuid not null default extensions.uuid_generate_v4 (),
name character varying(255) not null,
is_active boolean not null default true,
...,
updated_at timestamp with time zone null,
constraint buildings_pkey primary key (id),
constraint buildings_company_id_fkey foreign key (company_id) references companies (id) on delete cascade
);
create table public.equipments (
id uuid not null default extensions.uuid_generate_v4 (),
name character varying not null,
buildings_id uuid null,
...,
warranty_expiration date null,
constraint equipments_pkey primary key (id),
constraint equipments_company_id_fkey foreign key (company_id) references companies (id),
constraint equipments_location_id_fkey foreign key (buildings_id) references buildings (id)
) tablespace pg_default;
In a React application I’m running the following query:
query = supabase
.from("equipments")
.select("*, buildings!inner(name)")
.eq("company_id", "20ebc2a2-7dcc...")
.order("name", { referencedTable: "buildings", ascending: true, nullsFirst: false });
The query runs without error, but the .order() doesn’t seem to work.
I also tried:
.order("name", { referencedTable: "equipments_location_id_fkey", ascending: true, nullsFirst: false });
.order("buildings.name", { ascending: true});
And that didn’t work either... What can I do?
4 Replies
https://supabase.com/blog/postgrest-11-prerelease#order-by-related-tables
That method only works on one to one and many to one.
Thanks for your response.
The relationship equipments.buildings.id → buildings.id is many-to-one, which does allow sorting by a column in the related table.
The error was in:
.order("buildings.name") directly with the dot notation, but I should have used the syntax shown in the documentation.
const { data, error } = await supabase
.from("equipments")
.select(
*,
buildings(name)
)
.order("buildings(name)", { ascending: true });I should have been clearer that syntax was what I was pointing out, rather than just mention the limit to it.
I'm sorry, maybe I didn't express myself well. On the contrary, thank you very much. Your help was very important to me.