CODEWITHSUNDEEP
sqlstringpostgresqlsubquerystring-aggregation
Hugo Licon
Hugo Licon

Reputation: 181

One-to-Many SQL SELECT concatenated into single row

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|        |

Upvotes: 1

Views: 3487

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659217

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);

Related:

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:

Upvotes: 1

GMB
GMB

Reputation: 222702

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

Upvotes: 2

Related Questions