One-to-Many SQL SELECT concatenated into single row

Question

I'm using Postgres and I have the following schemes.

Orders

| id |    status   |
|----|-------------|
|  1 |  delivered  |
|  2 | recollected |

Comments

| id |   text  | user | order |
|----|---------|------|-------|
|  1 | texto 1 |  10  |   20  |
|  2 | texto 2 |  20  |   20  |

So, in this case, an order can have many comments.

I need to iterate over the orders and get something like this:

| id |    status   |    comments    |
|----|-------------|----------------|
|  1 |  delivered  | text 1, text 2 |
|  2 | recollected |                |

I tried to use LEFT JOIN but it didn't work

SELECT
    Order.id,
    Order.status,
    "Comment".text
FROM  "Order" 
LEFT JOIN "Comment" ON Order.id = "Comment"."order"

it returns this:

| id |    status   | text   |
|----|-------------|--------|
|  1 |  delivered  | text 1 |
|  1 |  delivered  | text 2 |
|  2 |  recollected|        |

score 2 · Accepted Answer · answered Aug 03 '20 at 21:00

You are almost there - you just need aggregation:

SELECT
    o.id,
    o.status,
    STRING_AGG(c.text, ',') comments
FROM  "Order" o
LEFT JOIN "Comment" c ON p.id = c."order"
GROUP BY o.id, o.status

I would strongly recommend against having a table (and/or a column) called order: because it conflicts with a language keyword. I would also recommend avoiding quoted identifiers as much as possible - they make the queries longer to write, for no benefit.

Note that you can also use a correlated subquery:

SELECT
    o.id,
    o.status,
    (SELECT STRING_AGG(c.text, ',') FROM "Comment" c WHERE c."order" = p.id) comments
FROM  "Order" o

score 1 · Answer 2 · answered Aug 03 '20 at 21:51

You can make it work with LEFT JOIN and aggregate after the join. But it's typically more efficient to aggregate first and join later.

If most or all rows in "Comment" are involved:

SELECT o.id, o.status, c.comments
FROM   "Order" o
LEFT   JOIN (
   SELECT "order" AS id, string_agg(text, ', ') AS comments
   FROM   "Comment"
   GROUP  BY 1
   )  c USING (id);

Indexes won't matter, while most rows have to be read anyway.

For only a small percentage of rows (like, if you have a selective filter on "Order"):

SELECT o.id, o.status, c.comments
FROM   "Order" o
LEFT   JOIN LATERAL (
   SELECT string_agg(text, ', ')  AS comments
   FROM   "Comment"
   WHERE  "order" = o.id
   )  c ON true
WHERE  <some_selective_filter>;

In this case, be sure to have an index on ("Comment"."order"), or more specialized, a covering index including text:

 CREATE INDEX foo ON "Comment" ("order") INCLUDE (text);

Aside: Consider legal, lower-case, unquoted identifiers in Postgres. In particular, don't (ab-)use completely reserved SQL keywords like ORDER as identifier. Much clearer and less potential for sneaky errors. See:

Are PostgreSQL column names case-sensitive?

One-to-Many SQL SELECT concatenated into single row

2 Answers2

Linked