I am trying to retrieve the related data of the orders, customers, and orderItems according to the logged in station owner. The expected display of the data are the order_id, created_at(orders), details of the orderItems such as the corresponding quantity and the name of the type which can be related from the type_id, and the details of the customer
Data definition:
create table
public.orders (
order_id uuid not null default gen_random_uuid (),
created_at timestamp with time zone not null default (now() at time zone 'utc'::text),
remarks text not null default ''::text,
water_station_id uuid not null,
customer_id uuid not null,
total numeric null,
constraint orders_pkey primary key (order_id),
constraint orders_customer_id_fkey foreign key (customer_id) references customers (customer_id) on update cascade on delete restrict,
constraint orders_station_id_fkey foreign key (station_id) references station (id) on update cascade on delete cascade
) tablespace pg_default;
create table
public.order_items (
order_items_id uuid not null default gen_random_uuid (),
quantity numeric not null,
order_id uuid not null,
type_id uuid null,
station_id uuid null,
constraint order_items_pkey primary key (order_items_id),
constraint order_items_order_id_fkey foreign key (order_id) references orders (order_id) on update cascade on delete cascade,
constraint order_items_station_id_fkey foreign key (station_id) references station (id) on update cascade on delete set default,
constraint order_items_type_id_fkey foreign key (type_id) references type (id) on update restrict on delete restrict
) tablespace pg_default;
create table
public.customers (
customer_id uuid not null default gen_random_uuid (),
"firstName" text not null,
"lastName" text not null,
contact_no numeric not null,
delivery_mode text not null,
address text not null,
constraint customers_pkey primary key (customer_id),
constraint customers_contact_no_key unique (contact_no)
) tablespace pg_default;
Each station has a user_id which means that they have an account and I am only trying to retrieve the data that is related to the specific station.
The problem here is that I am unable to retrieve the various types and the information of the customer who placed that order.
const {data, error} = await supabase
.from('station')
.select(`id,
orders(
*
),
order_items(
quantity,
type_id
)
`)
.eq('user_id', session?.user.id )
