Postgres - Return the results from multiple rows

Question

I have the below tables:

CREATE SCHEMA pulse;

CREATE TABLE pulse.event(
id integer,
url text,
name text, 
event_start  date, 
event_end   date,
sub_type text,
preference jsonb
);

CREATE TABLE pulse.event_meta(
event_id integer,
data json
);

CREATE TABLE pulse.slot_archive(
id integer,
event_id integer,
location_id integer, 
date text,
start_time text,
end_time text,
current_registration integer,
preference json
);

CREATE TABLE pulse.event_booking_archive(
id integer,
event_id integer,
slot_id integer, 
status text,
data json
);

The below query retrieves the event when the status is canceled (From the event table, preference column) along with additional data from other tables.

Query:

Select 
COALESCE((evnt.preference::json #>> '{eventStatus,status}' is not null)::boolean, false) as "eventCancelled", 
COALESCE(attendancecount,0) as "attendanceCount", 
COALESCE((meta.data ->> 'walkins')::int, 0) as walkins, 
COALESCE((meta.data ->> 'attendanceSubmitted')::boolean, false) as "attendanceSubmitted", 
meta.data -> 'heroImage' as "heroImage", 
meta.data -> 'tileContent' -> 'registrationPage' ->> 'title' as title, 
evnt.id as "eventId", 
evnt.url as "eventUrl", 
evnt.name as name, 
evnt.event_start AT TIME ZONE 'America/New_York' as "startTime",
evnt.event_end AT TIME ZONE 'America/New_York' as "endTime",  
evnt.sub_type as "subType", 
agg_slot.slotDates as slots, 
agg_slot.registrationcount as "registrationCount" from pulse.event as evnt 
inner join pulse.event_meta meta on evnt.id = meta.event_id 
left join (select event_id, COALESCE(sum((data ->> 'attendanceCount')::int),0) as attendancecount from pulse.event_booking_archive where status = 'SUCCESS' group by event_id) as eb 
on evnt.id = eb.event_id, 
(select event_id, location_id, array_agg(CONCAT_WS(' ', slot.date,slot.start_time,slot.end_time)) as slotDates, sum(current_registration) as registrationCount from pulse.slot_archive as slot 
group by slot.event_id, slot.location_id) as agg_slot 
where evnt.id = agg_slot.event_id 
and evnt.id in (select id from pulse.event where event_end + interval '48h' < now()) 
and agg_slot.location_id = '3305';

But, I need help in finding out the event whose event location is canceled. i.e All the slots for that particular event location_id in the slot_archive table have "statusMeta": "CS". I'm stuck here. Can someone please assist?

https://www.db-fiddle.com/f/iqxvqrjDrqb8B3tG1xzpHN/14

Firstly, there's a field that doesn't exist in your db-fiddle definition : event.type, that you use in your request. Secondly, use CTE and avoid to use sub-request, it's difficult to read — Ontologiae
– Ontologiae, Commented Oct 31, 2022 at 16:46
I have updated my db- fiddle request by removing the evnt.type — Aishu
– Aishu, Commented Oct 31, 2022 at 16:58
Because I wanted to return the slotDates & registrationcount from slot_archive table. (agg_slot.slotDates as slots, agg_slot.registrationcount as "registrationCount") — Aishu
– Aishu, Commented Oct 31, 2022 at 17:00

Ontologiae · Accepted Answer · 2022-10-31 18:05:26Z

I rewrote your request to makes it more clear. So we have :

With booking_archive_success as 
(select event_id, COALESCE(sum((data ->> 'attendanceCount')::int),0) as attendancecount 
                    from pulse.event_booking_archive where status = 'SUCCESS' 
                        group by event_id)
, agg_slot as (
    select slot.event_id, slot.location_id
            , array_agg(CONCAT_WS(' ', slot.date,slot.start_time,slot.end_time)) as slotDates
            , sum(slot.current_registration) as registrationCount
            , array_agg(slot.preference->'slotStatus'->>'statusMeta') as statuses
            from pulse.slot_archive as slot 
            group by slot.event_id, slot.location_id)
select COALESCE((evnt.preference::json #>> '{eventStatus,status}' is not null)::boolean, false) as "eventCancelled"
    , COALESCE(attendancecount,0) as "attendanceCount"
    , COALESCE((meta.data ->> 'walkins')::int, 0) as walkins
    , COALESCE((meta.data ->> 'attendanceSubmitted')::boolean, false) as "attendanceSubmitted"
    , meta.data -> 'heroImage' as "heroImage"
    , meta.data -> 'tileContent' -> 'registrationPage' ->> 'title' as title
    , evnt.id as "eventId"
    , evnt.url as "eventUrl"
    , evnt.name as name
    , evnt.event_start AT TIME ZONE 'America/New_York' as "startTime"
    ,evnt.event_end AT TIME ZONE 'America/New_York' as "endTime"
    --, evnt.type as type
    , evnt.sub_type as "subType"
    , agg_slot.slotDates as slots
    , agg_slot.registrationcount as "registrationCount"
    , agg_slot.statuses
from pulse.event as evnt 
    inner join pulse.event_meta meta on evnt.id = meta.event_id 
    left join booking_archive_success as eb on evnt.id = eb.event_id
    inner join agg_slot on evnt.id = agg_slot.event_id
where evnt.id in 
                (select id from pulse.event where event_end + interval '48h' < now())
    and not exists (Select 1 from (Select unnest(agg_slot.statuses) as  statusMeta from agg_slot a Where a.event_id = evnt.id) t
                where statusMeta not like 'CS' or statusMeta is null 
               )
and agg_slot.location_id = '3305';

Several good habits to take :

Prefer the using of CTE when it is possible. The code is more readable, and easier to conceive
Avoid Cartesian Product in the from with the join condition in the where, so I fixed it by using an inner join clause

To solve (I hope) your problem, I used the unnest function in a "not exists" condition in the where. The goal is to forbid every eventid where one of the statusMeta is not CS

Collectives™ on Stack Overflow

Postgres - Return the results from multiple rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related