I have these tables
events:
id
title
content
status
creator = ForeignKey(users.c.id)
location_id = ForeignKey(locations.c.id)
activities_id = ForeignKey(activities.c.id)
....
users:
id
...
accounts:
id
user_id = ForeignKey(users.c.id)
name
surname
location:
id
city
street
activities:
id
name
event_users
id
events_id = ForeignKey(events.c.id)
users_id = ForeignKey(users.c.id)
what I'm trying to do
There is an activity (table events ). The event has a creator (table users ) associated with the accounts table. The event also has participants (table events_user ). I need to pull information on the event, data on its creator (name and surname) and data on the participants of the event (also name and surname) in one query. And I use the json object representation for easy further processing of the result
what I've got
SELECT
events.id, events.title, events.creator, events.content, events.status,
(json_agg(json_build_object('city', locations.city, 'street', locations.street)) -> 0)AS location,
(json_agg(json_build_object('id', activities.id, 'name', activities.name)) -> 0) AS activity,
(json_agg(json_build_object('name', accounts.name, 'surname', accounts.surname)) -> 0) AS creator
FROM
events
JOIN
locations ON events.location_id=locations.id
JOIN
activities ON events.activities_id=activities.id
JOIN
accounts ON events.creator=accounts.user_id
WHERE events.id = :pk GROUP BY events.id
result
{'id': 1, 'title': 'fffffff', 'creator': '{"name" : null, "surname" : null}', 'content': 'dddddddddddddd', 'status': 'OPEN', 'location': '{"city" : "Warsaw", "street" : "jezewskiego"}', 'activity': '{"id" : 1, "name" : "baskeball"}'}
But I can't figure out how I can add partisipate members, a many-to-many (link tables events_users and accounts ) here to get smth like
{'id': 1, 'title': 'fffffff', 'creator': '{"name" : Name, "surname" : Surname}', 'content': 'dddddddddddddd', 'status': 'OPEN', 'location': '{"city" : "Warsaw", "street" : "jezewskiego"}', 'activity': '{"id" : 1, "name" : "baskeball"}', 'members': ['{"id": 1, "name" : Name1, "surname" : Surname1}, {"id": 2, "name" : Name2, "surname" : Surname2}']}