Query for all N elements in an M:N relation

Question

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?

Answer 1

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

Answer 2

You have converted most of GMB's answer successfully to Opaleye in your answer to your subsequent question. Here's a fully-working version in Opaleye.

In the future you are welcome to ask such questions on Opaleye's issue tracker. You will probably get a quicker response there.

{-# LANGUAGE Arrows #-}
{-# LANGUAGE FlexibleInstances #-}
{-# LANGUAGE MultiParamTypeClasses #-}
{-# LANGUAGE TemplateHaskell #-}

import           Control.Arrow
import qualified Opaleye as OE
import qualified Data.Profunctor as P
import           Data.Profunctor.Product.TH (makeAdaptorAndInstance')

type F field = OE.Field field

data TaggedArticle a b c =
  TaggedArticle { articleFk :: a, tagFk :: b, createdAt :: c}
type TaggedArticleR = TaggedArticle (F OE.SqlInt8) (F OE.SqlInt8) (F OE.SqlDate)

data Tag a b = Tag { tagKey :: a, tagName :: b }
type TagR = Tag (F OE.SqlInt8) (F OE.SqlText)

$(makeAdaptorAndInstance' ''TaggedArticle)
$(makeAdaptorAndInstance' ''Tag)

tagsTable :: OE.Table TagR TagR
tagsTable = error "Fill in the definition of tagsTable"

taggedArticlesTable :: OE.Table TaggedArticleR TaggedArticleR
taggedArticlesTable = error "Fill in the definition of taggedArticlesTable"

-- | Query all tags.
allTagsQ :: OE.Select TagR
allTagsQ = OE.selectTable tagsTable

-- | Query all article-tag relations.
allTaggedArticlesQ :: OE.Select TaggedArticleR
allTaggedArticlesQ = OE.selectTable taggedArticlesTable

-- | Join article-ids and tag names for all articles.
articleTagNamesQ :: OE.Select (F OE.SqlInt8, F OE.SqlText, F OE.SqlDate)
articleTagNamesQ = proc () -> do
  ta <- allTaggedArticlesQ -< ()
  t  <- allTagsQ -< ()
  OE.restrict -< tagFk ta OE..=== tagKey t -- INNER JOIN ON
  returnA -< (articleFk ta, tagName t, createdAt ta)

-- | Aggregate all tag names for all articles
articleTagsQ :: OE.Select (F OE.SqlInt8, F (OE.SqlArray OE.SqlText))
articleTagsQ =
  OE.aggregate ((,) <$> P.lmap (\(i, _, _) -> i) OE.groupBy
                    <*> P.lmap (\(_, t, _) -> t) OE.arrayAgg)
      (OE.limit 10 (OE.orderBy (OE.desc (\(_, _, ca) -> ca)) articleTagNamesQ))