In this query I want to receive 10 records of machine locations that each have a bunch of meals connected to them. These meal records are connected to a certain dish type. And I want to see the live meals inventory of these locations.
To do this I first joined the meals and dishes table this way I have the names of each meal. Then I joined the meal and the location_dishes to see what location each meal could fit. Finally I joined the locations table to get the name of the location.
The furthest I've gotten for now is this query:
select
l.name as location_name,
d.name as dish_name,
ld.suggested_quantity,
count(m.truck_id) as in_transport_quantity,
count(m.machine_id) as stocked_quantity
from
public.meals as m
inner join
public.dishes as d on d.id = m.dish_id
inner join
public.location_dishes as ld on d.id = ld.dish_id
inner join
public.locations as l on l.id = ld.location_id
where
l.type = 'Machine'
group by
l.name, d.name, ld.suggested_quantity
This query returns 45 records because the location rows still have multiple meal matches. I want to join the meal data in an array of object so I can easily loop over it in my Vue frontend. The following query is my best effort but it errors because I can't use count() inside another aggregate. How can I solve this issue ?
select
l.name as location_name,
array_agg(json_build_object('dish_name', d.name,
'suggested_quantity', ld.suggested_quantity,
'in_transport_quantity', count(m.truck_id),
'stocked_quantity', count(m.machine_id)
)) as machine_plan
from
public.meals as m
inner join
public.dishes as d on d.id = m.dish_id
inner join
public.location_dishes as ld on d.id = ld.dish_id
inner join
public.locations as l on l.id = ld.location_id
where
l.type = 'Machine'
group by
l.name;
For extra info I'll include all the SQL for the relevant tables as well:
create table public.meals
(
id uuid not null default gen_random_uuid (),
dish_id uuid not null,
supplier_batch character varying not null,
expires_at timestamp with time zone not null,
warehouse_id uuid not null,
stored_at timestamp with time zone not null default now(),
truck_id uuid null,
transported_at timestamp with time zone null,
machine_id uuid null,
filled_at timestamp with time zone null,
sale_id uuid null,
picked_up_at timestamp with time zone null,
created_at timestamp with time zone not null default now(),
updated_at timestamp with time zone not null default now(),
constraint meals_pkey primary key (id),
constraint meals_dish_id_fkey
foreign key (dish_id) references dishes (id)
on update cascade on delete cascade,
constraint meals_machine_id_fkey
foreign key (machine_id) references locations (id)
on update cascade on delete cascade,
constraint meals_sale_id_fkey
foreign key (sale_id) references sales (id)
on update cascade on delete cascade,
constraint meals_truck_id_fkey
foreign key (truck_id) references locations (id)
on update cascade on delete cascade,
constraint meals_warehouse_id_fkey
foreign key (warehouse_id) references locations (id)
on update cascade on delete cascade
) tablespace pg_default;
create table public.dishes
(
id uuid not null default gen_random_uuid (),
name character varying not null,
description character varying not null,
purchase_price double precision not null,
sell_price double precision not null,
calories bigint null,
protien bigint null,
fat bigint null,
carbohydrates bigint null,
points bigint not null,
food_category_id uuid not null,
supplier_id uuid not null,
created_at timestamp with time zone not null default now(),
updated_at timestamp with time zone not null default now(),
thumbnail_path character varying not null,
constraint dishes_pkey primary key (id),
constraint dishes_food_category_id_fkey
foreign key (food_category_id) references food_categories (id)
on update cascade on delete cascade,
constraint dishes_supplier_id_fkey
foreign key (supplier_id) references suppliers (id)
on update cascade on delete cascade
) tablespace pg_default;
create table public.locations
(
id uuid not null default gen_random_uuid (),
type character varying not null,
name character varying not null,
capacity bigint null,
street character varying null,
street_number character varying null,
postal_code character varying null,
city character varying null,
country character varying null,
created_at timestamp with time zone not null default now(),
updated_at timestamp with time zone not null default now(),
geolocation geography null,
constraint locations_pkey primary key (id)
) tablespace pg_default;
create table public.location_dishes
(
location_id uuid not null,
dish_id uuid not null,
suggested_quantity bigint not null,
created_at timestamp with time zone not null default now(),
updated_at timestamp with time zone not null default now(),
constraint location_dishes_pkey primary key (location_id, dish_id),
constraint location_dishes_dish_id_fkey
foreign key (dish_id) references dishes (id)
on update cascade on delete cascade,
constraint location_dishes_location_id_fkey
foreign key (location_id) references locations (id)
on update cascade on delete cascade
) tablespace pg_default;
The exact result I'm trying to achieve in my frontend is:
{
machine_id:
machine_name:
machine_capacity:
machine_plan: {
dish_id:
dish_name:
suggested_quantity:
in_transport_quantity:
stocked_quantity:
}
}
You forgot food_categories, suppliers and sales and most importantly, some sample/mock records for this to be reproducible. To get rid of the error, you should be able to just nest the subquery:
select location_name,
jsonb_agg(machine_plan)machine_plan
from (
select
l.name as location_name,
json_build_object('dish_name', min(d.name),
'suggested_quantity', min(ld.suggested_quantity),
'in_transport_quantity', count(m.truck_id),
'stocked_quantity', count(m.machine_id)
) as machine_plan
from public.meals as m
inner join public.dishes as d on d.id = m.dish_id
inner join public.location_dishes as ld on d.id = ld.dish_id
inner join public.locations as l on l.id = ld.location_id
where l.type = 'Machine'
group by l.name
)_ group by 1;