I’m trying to get a list of users with a list of their associated seasons in Postgres. They’re connected to a pivot table.
I am completely lost on how to do the query. I know just joining all 3 tables tables in a query will return all the rows I need, so technically I could just do an array.reduce with javascript, but I think a proper query might be more efficient.
Any ideas?
tables:
users
| id | name |
|---|---|
| 1 | Jake |
| 2 | Adam |
seasons
| id | name |
|---|---|
| 1 | Fall |
| 2 | Spring |
user_season
| user_id | season_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 2 |
Desired Output
to send to frontend:
| user_id | user_name | seasons |
|---|---|---|
| 1 | Jake | [{ name: “Fall”, id: 1}, {name: “Spring”, id: 2}] |
| 2 | Adam | [{name: “Spring”, id: 2}] |
CodePudding user response：
Here is one approach using STRING_AGG:
SELECT u.id AS user_id, u.name AS user_name,
'[' || STRING_AGG('{ "name": "' || s.name || '", "id":' || s.id ||
'}', ', ') || ']' AS seasons
FROM users u
LEFT JOIN user_season us ON us.user_id = u.id
LEFT JOIN seasons s ON s.id = us.season_id
GROUP BY u.id, u.name
ORDER BY u.id;
