CODEWITHSUNDEEP
postgresql
TheUnreal
TheUnreal

Reputation: 24472

Postgres - Query one to many relationship

I'm trying to make a query to get a report and all of the report_items associated with the report.

reports table structure: id | name | creation_date

report_items table structure: id | report_id | place | producer | serial_number

I tried this:

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

but only the first report_item returns (instead of a list of 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"}}

Expected result:

{"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"}]}

Any idea what I'm missing?

a long solution I can do (pretty sure it's not ideal..):

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

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

and combine them programmatically.

Upvotes: 3

Views: 2721

Answers (1)

Ancoron
Ancoron

Reputation: 2733

If I understand the question properly, the desired result is a single row for the report data including all items as a JSON array.

Under that assumption, a query as follows should work:

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}

...or as a sub-select (because PostgreSQL is not yet smart enough to push down the filter into the 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}

In both cases, the output column items will contain a JSON array of all related items.

If you want the complete report entry as JSON, try this (beware this only works for jsonb, not 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}

Upvotes: 2

Related Questions