How to use Postgres string_agg function on multiple tables?

Question

So I have a 3 tables that I select from to create a selection that looks like this:

The SQL statement used to get this selection (from 3 tables):

SELECT games.title as title, taggings.taggable_id as game_id, tags.name as category, tags.id
    FROM taggings, tags, games
    WHERE taggings.tag_id = tags.id and games.id = taggings.taggable_id

What I want to do is to make the selection combine all the categories fields. So there would only be one row for superorbit.io and it would contain a category field with "2d Shooter, Asteroids, Free For All, etc". Very much like what this guy was trying to do: How to concatenate strings of a string field in a PostgreSQL 'group by' query?

I'm trying to do that exact same thing, but with little luck. I think the major difference between my case is and his is that I'm working with 3 tables here (taggings, tags and games). Here's the query that I have so far (using the string_agg function) but it doesn't seem to concatenate the categories like you would expect it to. In fact, the results look almost the same as the first code.

  SELECT games.title as title, taggings.taggable_id as game_id, string_agg(tags.name, ', ') , tags.id
    FROM taggings, tags, games
    WHERE taggings.tag_id = tags.id and games.id = taggings.taggable_id  GROUP BY tags.name,games.title,taggings.taggable_id,tags.id

What am I doing wrong? I am using Postgres 9.6.7.

You’re telling the database to group by tag names so they will be treated separately. Remove that column from grouping since you’re aggregating it — Sami Kuhmonen, Jul 08 '18 at 19:32
Tried that. results are the same. Also tried removing GROUP BY altogether but i get an error: ERROR: column "games.title" must appear in the GROUP BY clause or be used in an aggregate function — Chris Scott, Jul 08 '18 at 19:37
when i do that, it shows a single category in each row (i.e. not concated like "2d Shooter, Asteroids, Free For All, etc") — Chris Scott, Jul 08 '18 at 20:43
nvm my last comment, i re-read your last comments and reconstructed the SQL statements properly. It works!! — Chris Scott, Jul 08 '18 at 20:46

score 0 · Answer 1 · answered Jul 08 '18 at 20:48

Thanks to Sami, was able to get the results I wanted with the following query:

  SELECT games.title as title, taggings.taggable_id as game_id, string_agg(tags.name, ', ')
    FROM taggings, tags, games
    WHERE taggings.tag_id = tags.id and games.id = taggings.taggable_id  GROUP BY games.title,taggings.taggable_id

How to use Postgres string_agg function on multiple tables?

1 Answers1