I have have a query that returns rows from a blog database that can match a number of different supplied criteria. The query works as expected to return the data I require.
A typical example of the sql:
Code SQL:SELECT cr.category_id , c.name AS category_name , p.post_id , p.added , p.title , ts_headline(ts.title, ts.ts_query) AS headline , ts.ts_query , ts.rank FROM ( SELECT post_id , title , url , added , ts_query , ts_rank_cd(search_tsv, ts_query) AS rank FROM public.posts, plainto_tsquery('this is a test post') AS ts_query WHERE search_tsv @@ ts_query ORDER BY rank DESC ) AS ts INNER JOIN public.category_relationships AS cr ON cr.post_id = ts.post_id INNER JOIN public.categories AS c ON cr.category_id = c.category_id INNER JOIN public.posts AS p ON cr.post_id = p.post_id WHERE cr.category_id = 9 AND p.added BETWEEN '2010-12-01 00:00:00' AND '2010-12-31 23:59:59' AND p.post_id IN ( SELECT post_id FROM public.tags WHERE tag = 'tag1' OR tag = 'tag2' OR tag = 'tag3') ORDER BY ts.rank, p.added DESC
While I can search for a blog post using the above based on tags as part of the search criteria, I do not include the tags in the returned data since I will get a row of data for a certain post_id for each tag that exists for that post_id.
What I would like to do is include the following as a subquery for returning a formatted string containing the tags for the relevant post:
Code SQL:SELECT array_to_string( array( SELECT tag FROM public.tags WHERE post_id = p.post_id ), ', ') AS tags
Of course, the error is obvious: the alias 'p' does not exist in the subquery scope. My dilemma is how do I incorporate this subquery into the larger query above and pull the p.post_id into the subquery? I'm sure I'm just being obtuse here, but any thoughts would be appreciated