Say I have the following tables that model tags attached to articles:
articles (article_id, title, created_at, content)
tags (tag_id, tagname)
articles_tags (article_fk, tag_fk)
What is the idiomatic way to retrieve the n newest articles with all their attached tag-names? This appears to be a standard problem, yet I am new to SQL and don't see how to elegantly solve this problem.
From an application perspective, I would like to write a function that returns a list of records of the form [title, content, [tags]], i.e., all the tags attache to an article would be contained in a variable length list. SQL relations aren't that flexible; so far, I can only think about a query to joint the tables that returns a new row for each article/tag combination, which I then need to programmatically condense into the above form.
Alternatively, I can think of a solution where I issue two queries: First, for the articles; second, an inner join on the link table and the tag table. Then, in the application, I can filter the result set for each article_id to obtain all tags for a given article? The latter seems to be a rather verbose and inefficient solution.
Am I missing something? Is there a canonical way to formulate a single query? Or a single query plus minor postprocessing?
On top of the bare SQL question, how would a corresponding query look like in the Opaleye DSL? That is, if it can be translated at all?
You would typically use a row-limiting query that selects the articles and orders them by descending date, and a join or a correlated subquery with an aggregation function to generate the list of tags.
The following query gives you the 10 most recent articles, along with the name of their related tags in an array:
select
a.*,
(
select array_agg(t.tagname)
from article_tags art
inner join tags t on t.tag_id = art.tag_fk
where art.article_fk = a.article_id
) tags
from articles
order by a.created_at desc
limit 10