Problems with Postgresql ERROR: subquery uses ungrouped column "ev.title" from outer query

0
votes

I have a query like this:

select c.id, c.name, c.website, c.longdescription, c.description, c.email, 
    (SELECT jsonb_agg(ev) FROM 
        (SELECT ev.title, ev.description, ev.longdescription, 
            (SELECT jsonb_agg(ed) FROM 
                (SELECT ed.startdate, ed.enddate, ed.id WHERE ed.id notnull)ed) as dates, ev.id WHERE ev.id notnull) ev) as events, 
    (SELECT jsonb_agg(ca) FROM (SELECT ct.zip, ca.id, ca.street1, ca.street2, ca.addresstype_id, ST_Y(ca.geopoint::geometry) as latitude, ST_X(ca.geopoint::geometry) as longitude 
    WHERE ca.id notnull)ca) as addresses
FROM companies c
LEFT JOIN events ev ON ev.company_id = c.id
LEFT JOIN companyaddresses ca ON ca.company_id = c.id
LEFT JOIN cities ct ON ct.id = ca.city_id
LEFT JOIN eventdates ed ON ed.event_id = ev.id
GROUP by c.id

I am getting the error "ERROR: subquery uses ungrouped column "ev.title" from outer query Position: 125".

Can't figure out how to group it correctly for the subqueries. Any suggestions?

1
postgresql

1 Answers

1
votes

Give this a try:

SELECT c.id, c.name, c.website, c.longdescription, c.description, c.email, 
    (SELECT jsonb_agg(ev) FROM 
        (SELECT even.title, even.description, even.longdescription, 
            (SELECT jsonb_agg(ed) FROM
                (SELECT eventdates.startdate, eventdates.enddate, eventdates.id FROM eventdates WHERE eventdates.event_id = even.id)ed) as dates, 
        even.id FROM events even WHERE even.company_id = c.id) ev) as events, 
jsonb_agg((SELECT ca FROM (SELECT ct.zip, ca.id, ca.street1, ca.street2, ca.addresstype_id, ST_Y(ca.geopoint::geometry) as latitude, ST_X(ca.geopoint::geometry) as longitude WHERE ca.id notnull)ca)) as addresses
FROM companies c
LEFT JOIN companyaddresses ca ON ca.company_id = c.id
LEFT JOIN cities ct ON ct.id = ca.city_id
Group by c.id