Postgres Select Distinct AND Order By Date-CodePudding

Table and columns of note:

pictures, tags, picture_tags

pictures.id
pictures.created_date

tags.id

picture_tags.tag_id
picture_tags.picture_id

I have the following query using a join table:

SELECT pictures.*, pictures.id as picture_id, tags.id as tag_id
FROM picture_tags
LEFT JOIN pictures ON pictures.id = picture_tags.picture_id
LEFT JOIN tags ON tags.id = picture_tags.tag_id
WHERE picture_tags.tag_id IN (1, 2)
GROUP BY pictures.id, tags.id
ORDER BY pictures.created_date ASC;

Since a picture can have multiple tags, this can return the same picture.id multiple times. Is there a way to prevent this so that picture.ids only show up once?

It is currently returning like this:

id | created_date | picture_id | tag_id
1 | 2022-12-08 19:04:23 | 1 | 1
1 | 2022-12-08 19:04:23 | 1 | 2
2 | 2022-12-09 00:46:30 | 2 | 3

My ideal return would be something like:

picture.created_date | picture.id | tagIds
2022-12-08 19:04:23 | 1 | [ 1, 2 ]
2022-12-09 00:46:30 | 2 | [3]

CodePudding user response：

As I said in a comment, you want to think carefully before combining rows like this. But if you really want to, you can do this:

SELECT p.id, p.created_date, string_agg(pt.tag_id, ',') as tag_ids
FROM pictures p
INNER JOIN picture_tags pt on pt.picture_id = p.id
WHERE pg.tag_id IN (1,2)
GROUP BY p.id, p.created_date
ORDER BY p.created_date

Note I converted the LEFT JOIN to INNER JOIN. In the original query, the first join made no sense as a LEFT JOIN (all the fields would be NULL) and the second join was effectively an INNER JOIN because of the WHERE clause. Additionally, since we didn't actually use any fields from the tag table I was able to remove that join completely.