sql - 有没有更好的方法来构建我的表，进行 4 表连接，或者我是否必须执行多个查询？

Question

执行此 GET 请求的正确方法是什么？我目前有 4 个表:

事件、事件_评论、事件_照片、事件_用户_进行

最后 3 个表，event_comments、event_photos 和 event_users_going 都有一个使用事件表主键的外键。

我不确定的是，我是否必须对所有 4 个表或 4 个单独的查询执行 JOIN？因为，每个表上的每个查询都会返回不同数量的行。

例如，第一个查询将针对事件:

SELECT * FROM events where e_id = 1

这将返回 1 行。

第二个查询:

SELECT * FROM event_photos where e_id = 1

返回 12 行

第三个查询:

SELECT * FROM event_comments WHERE e_id = 1

返回 20 行

第四次查询:

SELECT * FROM event_users_going WHERE e_id = 1

返回 30 行

Answer 1

这取决于接收结果的内容。如果它可以处理 JSON 并且这样做的开销不会破坏交易，您可以执行以下操作以将相关行包含为 JSON 数组:

SELECT *,
       COALESCE((
         SELECT json_agg(event_photos)
           FROM event_photos
          WHERE event_photos.e_id = events.e_id
       ), '[]') AS photos,
       COALESCE((
         SELECT json_agg(event_comments)
           FROM event_comments
          WHERE event_comments.e_id = events.e_id
       ), '[]') AS comments,
       COALESCE((
         SELECT json_agg(event_users_going)
           FROM event_users_going
          WHERE event_users_going.e_id = events.e_id
       ), '[]') AS going
  FROM events
 WHERE e_id = 1;

示例输出:

 e_id |          photos          |        comments         | going 
------+--------------------------+-------------------------+-------
    1 | [{"e_id":1,"url":"p1"}, +| [{"e_id":1,"txt":"c1"}] | []
      |  {"e_id":1,"url":"p2"}]  |                         | 
(1 row)

返回 JSON 时，您可以更进一步，使用单列构建结果:

SELECT json_build_object(
         'event', events,
         'photos', COALESCE((
           SELECT json_agg(event_photos)
             FROM event_photos
            WHERE event_photos.e_id = events.e_id
         ), '[]'),
         'comments', COALESCE((
           SELECT json_agg(event_comments)
             FROM event_comments
            WHERE event_comments.e_id = events.e_id
         ), '[]'),
         'going', COALESCE((
           SELECT json_agg(event_users_going)
             FROM event_users_going
            WHERE event_users_going.e_id = events.e_id
         ), '[]')
       )
  FROM events
 WHERE e_id = 1;

示例输出:

                              json_build_object                               
------------------------------------------------------------------------------
 {"event" : {"e_id":1}, "photos" : [{"e_id":1,"url":"p1"},                   +
  {"e_id":1,"url":"p2"}], "comments" : [{"e_id":1,"txt":"c1"}], "going" : []}
(1 row)