使用 PostgreSQL，我試圖從表“電影”中選擇所有資料以及來自其他 2 個多對多表的所有相關資料。
我的資料庫有 5 個表：movies、actors、studio、movies_actors、movies_studios。
ACTORS TABLE
-----------
| ID | NAME |
-----------
| 1 | Bob |
-----------
| 2 | John |
-----------
STUDIOS TABLE
----------------
| ID | NAME |
----------------
| 1 | studio A |
----------------
| 2 | studio B |
----------------
MOVIES TABLE
-----------
| ID | TITLE|
-----------
| 1 | aaa |
-----------
| 2 | bbb |
-----------
MOVIES-ACTORS TABLE (MANY-TO-MANY)
--------------------
| MOVIE_ID| ACTOR_ID |
--------------------
| 1 | 1 |
--------------------
| 1 | 2 |
--------------------
| 2 | 1 |
--------------------
MOVIES-STUDIOS TABLE (MANY-TO-MANY)
--------------------
| MOVIE_ID| STUDIO_ID|
--------------------
| 1 | 1 |
--------------------
| 1 | 2 |
--------------------
| 2 | 1 |
--------------------
我正在尋找的回應是這樣的：
[
{id: 1, title: "aaa", actors: [{id: 1, name: "Bob"}, {id: 2, name: "John"}]}, studios: [{id: 1, name: "studio A"}, {id: 2, name: "studio B"}]
{id: 2, title: "bbb", actors: [{id: 1, name: "Bob"}], studios: [{id: 2, name: "studio B"}]}
]
我將在這里分享我到目前為止的查詢，它可以很好地加入“演員”和“電影”，但我不知道如何使它與第三個表“作業室”一起作業。
SELECT movies.*, json_agg(json_build_object('name', actors.name, 'id', actors.id)) AS actors
FROM movies
LEFT OUTER JOIN movies_actors
ON movies.id = movies_actors.movie_id
LEFT OUTER JOIN actors
ON movies_actors.actor_id = actors.id
GROUP BY movies.id
基本上，我需要對“movies_actors”和“actors”做同樣的事情，但是我不能讓“GROUP BY movies.id”按預期作業。這就是問題所在。
uj5u.com熱心網友回復：
這應該可以解決問題，并且是一種常用技術（請參見此處的另一個示例）：
SELECT
movies.*,
movies_actors_JSON.actors_JSON,
movies_studios_JSON.studio_JSON
FROM
movies
LEFT JOIN
( -- one row per movie_id
SELECT
movies_actors.movie_id,
JSON_AGG(
JSON_BUILD_OBJECT(
'id', actors.id,
'name', actors.name
)
) AS actors_JSON
FROM
movies_actors
JOIN actors ON actors.id = movies_actors.actor_id
GROUP BY
movies_actors.movie_id
) movies_actors_JSON
ON movies.id = movies_actors_JSON.movie_id
LEFT JOIN
( -- one row per movie_id
SELECT
movies_studios.movie_id,
JSON_AGG(
JSON_BUILD_OBJECT(
'id', studios.id,
'name', studios.name
)
) AS studios_JSON
FROM
movies_studios
JOIN studios ON studios.id = movies_studios.studio_id
GROUP BY
movies_studios.movie_id
) movies_studios_JSON
ON movies.id = movies_studios_JSON.movie_id
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/510813.html