1

I have a problem that can be simulated with a system much like SO's: posts and tags. To get my posts, I might do something like this:

SELECT title, body, author FROM posts WHERE id = ?

And then get my tags very simply

SELECT tag_id FROM tags_on_posts WHERE post_id = ?

Let's say I have a limit like SO does where you can only have 5 tags per post. Is there anyway I can do this in a single query?

SELECT title, body, author, tag1, tag2, tag3, tag4, tag5 
  FROM posts 
  JOIN /*help*/ 
  WHERE id = ?
corsiKa
  • 81,495
  • 25
  • 153
  • 204
  • [Have you tried a SQL Pivot?](http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) they can be painful but are super useful. [You can also be dynamic with them](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – TheNorthWes Jun 19 '14 at 16:17
  • I neglected my database specific tag. I'm working on Postgres. Now that I know the term PIVOT, I've just opened up a new research avenue. – corsiKa Jun 19 '14 at 16:20
  • Ahh, well I hope those avenues help. If you know it will always be 5... you could just use temporary table to construct the two intermediary tables and then merge them into a third final one. – TheNorthWes Jun 19 '14 at 16:23
  • Several fixes in my answer – Clodoaldo Neto Jun 19 '14 at 16:35

1 Answers1

2

You can aggregate the tags into a string and split them at the application side

select
    title, body, author,
    string_agg(tag_id, ',') as tag_ids
from
    posts p
    inner join
    tags_on_posts top on p.id = top.post_id
where p.id = ?
group by 1, 2, 3

If the tag names are in a third table

select
    title, body, author,
    string_agg(tag_name, ',') as tag_names
from
    posts p
    inner join
    tags_on_posts top on p.id = top.post_id
    inner join
    tags t on t.id = top.tag_id
where p.id = ?
group by 1, 2, 3
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260