sqlpostgresqlsupabase

Aggregate functions cannot be nested PostgreSQL


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:
  }
}

Solution

  • 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;