I thought this would be obvious but I am not able to figure this out.

I have the following many to many setup. A collection of images can hold a series of tags.

-- Images.
CREATE TABLE ImageContent (
    Id SERIAL PRIMARY KEY,
    Title TEXT NOT NULL,
    Description TEXT,
    ImageUrl TEXT NOT NULL,
    CreationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The tags are stored in a seperate table.

-- All tags to be used.
CREATE TABLE ContentTags (
    Id SERIAL PRIMARY KEY,
    TagName TEXT UNIQUE NOT NULL
);

The tags and images are tied together with the following joining table.

-- Many-to-many tags relation to a image
CREATE TABLE ImageTags (
    imageId INTEGER,
    TagID INTEGER,
    FOREIGN KEY (imageID) REFERENCES ImageContent
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY (TagID) REFERENCES ContentTags
        ON DELETE CASCADE,
    PRIMARY KEY (imageId, TagID)
);

Now I wish to get the image rows with the associated tag names in a single column.

Currently I am getting a row per tag as result and then using a groupby in code. Appending groupby to the current query itself throws a error.

SELECT img.*, c.tagname
FROM imagecontent img
JOIN imagetags i on img.id = i.imageid
JOIN contenttags c on c.id = i.tagid;

CodePudding user response：

You can join to the result of an aggregation:

select img.*, t.tags
from imagecontent img
  left join (
    select it.imageid, string_agg(ct.tagname, ',') as tags  
    from imagetags it
      join contenttags ct on it.tagid = ct.id
    group by it.imageid
  ) t on t.imageid = img.id
order by img.id