请求按反应类型分组的SQL原生查询及ORM关联修正

由于你的ORM关联配置错误（@hasMany关联了错误的模型，且外键设置不符合表结构），直接使用preload无法得到期望的结构化数据。以下是针对不同数据库的原生SQL解决方案：

PostgreSQL版本 #

SELECT
    p.id,
    p.post,
    COALESCE(
        json_agg(
            json_build_object(
                'reaction', r.reaction,
                'total_count', r.total_count
            )
        ) FILTER (WHERE r.reaction IS NOT NULL),
        '[]'::json
    ) AS reactions
FROM posts p
LEFT JOIN (
    -- 先按帖子ID和反应类型分组统计总数
    SELECT
        post_id,
        reaction,
        COUNT(*) AS total_count
    FROM reactions
    GROUP BY post_id, reaction
) r ON p.id = r.post_id
GROUP BY p.id, p.post;

MySQL版本 #

SELECT
    p.id,
    p.post,
    COALESCE(
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'reaction', r.reaction,
                'total_count', r.total_count
            )
        ),
        '[]'
    ) AS reactions
FROM posts p
LEFT JOIN (
    -- 先按帖子ID和反应类型分组统计总数
    SELECT
        post_id,
        reaction,
        COUNT(*) AS total_count
    FROM reactions
    GROUP BY post_id, reaction
) r ON p.id = r.post_id
GROUP BY p.id, p.post;

说明 #

1. 子查询先对reactions表按post_id和reaction分组，统计每种反应的总数
2. 主查询通过LEFT JOIN关联帖子表，确保即使没有任何反应的帖子也会被返回
3. 使用JSON聚合函数将每个帖子的反应统计结果组装成数组，COALESCE处理无反应的情况，返回空数组[]
4. 查询结果的结构完全匹配你期望的格式：每个帖子对象包含id、post字段，以及reactions数组（数组元素为反应类型和对应总数）

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