时间戳

首页 > 解决方案 > Postgres - 查询一对多关系

postgresql - Postgres - 查询一对多关系

问题描述

我正在尝试进行查询以获取与报告相关联的report所有report_items内容。

reports表结构：id | 姓名 | 创建日期

report_items表结构：id | 报告ID | 地点 | 制片人 | 序列号

我试过这个：

SELECT "reports".*,
          to_json("report_items".*) as "items"
          FROM "reports" INNER JOIN "report_items" USING ("id")
          WHERE "reports".id = ${req.params.id}

但只有第一个report_item 返回（而不是report_items 列表）：

{"id":1,"type":"fd","name":"dsfdsfds","client_name":"fdsfds","website":"dsffds","creation_time":"2019-03-12T22:00:00.000Z","items":{"id":1,"report_id":1,"place":"saddsa","type":"sdadsa","producer":"sdadsa","serial_number":"adsdsa","next_check_date":"2019-03-19","test_result":"saddsa","comments":"saddsa"}}

预期结果：

{"id":1,"type":"fd","name":"dsfdsfds","client_name":"fdsfds","website":"dsffds","creation_time":"2019-03-12T22:00:00.000Z","items": [{"id":1,"report_id":1,"place":"saddsa","type":"sdadsa","producer":"sdadsa","serial_number":"adsdsa","next_check_date":"2019-03-19","test_result":"saddsa","comments":"saddsa"}, {"id":1,"report_id":1,"place":"saddsa","type":"sdadsa","producer":"sdadsa","serial_number":"adsdsa","next_check_date":"2019-03-19","test_result":"saddsa","comments":"saddsa"}]}

知道我错过了什么吗？

我可以做一个很长的解决方案（很确定它不理想..）：

SELECT * FROM reports
        WHERE id = ${req.params.id}

SELECT * FROM report_items
      WHERE report_id = ${req.params.id}

并以编程方式组合它们。

标签： postgresql

解决方案

如果我正确理解了这个问题，那么所需的结果是报告数据的单行，包括作为 JSON 数组的所有项目。

在这种假设下，如下查询应该有效：

WITH data (id, items) AS (
    SELECT report_id, array_to_json(array_agg(to_json(*)))
    FROM report_items WHERE report_id = ${req.params.id}
    GROUP BY report_id
)
SELECT reports.*, data.items
FROM reports
INNER JOIN data ON (reports.id = data.report_id)
WHERE reports.id = ${req.params.id}

...或作为子选择（因为 PostgreSQL 还不够聪明，无法将过滤器下推到 CTE 中：

SELECT reports.*, data.items
FROM reports
INNER JOIN (SELECT report_id, array_to_json(array_agg(to_json(report_items.*)))
    FROM report_items
    GROUP BY report_id) AS data (id, items) ON (reports.id = data.id)
WHERE reports.id = ${req.params.id}

在这两种情况下，输出列items都将包含所有相关项目的 JSON 数组。

如果您希望完整的报告条目为 JSON，请尝试以下操作（注意这仅适用于jsonb，不适用于json）：

SELECT jsonb_set(to_jsonb(reports.*), '{items}', data.items, true) AS report_data
FROM reports
INNER JOIN (SELECT report_id, array_to_json(array_agg(to_json(report_items.*)))::jsonb
    FROM report_items
    GROUP BY report_id) AS data (id, items) ON (reports.id = data.id)
WHERE reports.id = ${req.params.id}

推荐阅读