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?

Please tag your question with the database you are running: mysql, oracle, postgresql...? — GMB, Jul 29 '20 at 21:21

score 2 · Accepted Answer · answered Jul 29 '20 at 21:25

2

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

answered Jul 29 '20 at 21:25

GMB

216,147
25
84
135

1

beat me to it.. i was going to suggest reading about LISTAGG - although i think this is not optimal because it forces some post processing – Randy Jul 29 '20 at 21:27
Thanks very much. I suppose array_agg is specific to PostgreSQL? Is there an equivalent expression in standard SQL? – Ulrich Schuster Jul 29 '20 at 21:28
1

@UlrichSchuster: string (resp. array) aggregation functions are highly database specific... I don't know what ANSI SQL specifies in that regard (maybe string aggregation with `listagg()`), but is is sure not widely implemented by database vendors. – GMB Jul 29 '20 at 21:42

score 1 · Answer 2 · answered Aug 08 '20 at 08:03

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))

Query for all N elements in an M:N relation

2 Answers2