如何在PostgreSQL中关联聚合posts与upvotes表单查询获取帖子点赞数

完全可以通过单次查询实现，首先需要注意你给出的upvotes表建表语句有个小疏漏：post_id字段定义末尾缺少逗号，实际部署时需要补上才能正常建表。具体实现方案如下：

方案1：LEFT JOIN 聚合查询（最常用，性能更好） #

通过左连接关联帖子表和点赞表，按帖子维度分组统计点赞数，没有点赞的帖子会自动返回0的点赞数，不会漏数据：

SELECT 
    p.id,
    p.content,
    COUNT(u.id) AS upvotes
FROM posts p
LEFT JOIN upvotes u ON p.id = u.post_id
-- 如需查询单个帖子，取消注释下方条件并替换为目标帖子ID
-- WHERE p.id = 1234
GROUP BY p.id, p.content;

方案2：子查询实现 #

如果不想写GROUP BY逻辑，也可以用关联子查询实现，写法更简洁：

SELECT
    p.id,
    p.content,
    (SELECT COUNT(*) FROM upvotes u WHERE u.post_id = p.id) AS upvotes
FROM posts p
-- 如需查询单个帖子，取消注释下方条件并替换为目标帖子ID
-- WHERE p.id = 1234

优化建议 #

数据量较大时，建议给upvotes表的post_id字段添加索引，可大幅提升查询性能：

CREATE INDEX idx_upvotes_post_id ON upvotes(post_id);

直接返回JSON格式 #

如果需要数据库直接返回你要求的JSON结构，不需要后端二次转换，可以用PostgreSQL自带的JSON函数实现：

SELECT json_build_object(
    'id', p.id,
    'content', p.content,
    'upvotes', COUNT(u.id)
) AS post_json
FROM posts p
LEFT JOIN upvotes u ON p.id = u.post_id
WHERE p.id = 1234
GROUP BY p.id, p.content;

内容的提问来源于stack exchange，提问作者user17359299