All we need is an easy explanation of the problem, so here it is.
I have a table of people :
I have a table of tags:
there is a junction table for the tags that connects people and tags:
id, person_id, tag_id
I have a query to get a group of people based on different search criteria so it’s being built up dynamically and what I would like to do is within that query – for each person – I’d like to be able to grab all of the tag names associated w/ that person. I’d like to grab all the associated tags and stick them into one column for each person row (preferably as an array or object). What is the general pattern to be able to return a group of stuff as one column when cycling through rows like this. I’m having trouble figuring out what to search for to find a generalized answer online. I’m using postgres.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
As others have mentioned, your goal is to denormalize the data and is not a usual use case for a relational database system, but it is certainly achievable. So if you really want to accomplish that, what you’d want to Google is something along the lines of "PostgreSQL concatenate multiple rows". The goal usually is to create a comma separated values list (CSV or something of a similar structure).
For historical reference, here is Erwin’s example from the aforementioned StackOverflow answer, leveraging
string_agg() to smush multiple
Actor values into a single field per
Movie row in a table of
SELECT movie, string_agg(actor, ', ') AS actor_list FROM tbl GROUP BY 1;
In your case, you’d want to
people to your
tags table (via your junction table). From the above example
movie would be equivalent to your
person_name field, and
actor would be equivalent to your
tag_name field. A query likely similar to this is what you’re looking for:
SELECT P.person_name, string_agg(T.tag_name, ', ') AS tag_list FROM people P LEFT JOIN peopleTagJunctionTable JT ON P.id = JT.person_id LEFT JOIN tags T ON JT.tag_id = T.id GROUP BY P.person_name;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂