查询M:N关系中的所有N个元素

Question

假设我有以下表格，这些表格模拟附加到文章的标签：

articles (article_id, title, created_at, content)
tags (tag_id, tagname)
articles_tags (article_fk, tag_fk)

检索n最新文章及其所有附加标记名的惯用方法是什么？这似乎是一个标准问题，但我是SQL新手，看不到如何优雅地解决这个问题。

从应用程序的角度来看，我想编写一个函数来返回[title, content, [tags]]形式的记录列表，也就是说，附加到文章的所有标记都将包含在一个可变长度列表中。SQL关系没有那么灵活；到目前为止，我只能考虑使用查询来联接为每个文章/标记组合返回新行的表，然后我需要以编程方式将其压缩成上面的形式。

或者，我可以想出一种解决方案，发出两个查询:第一，针对文章；第二，对链接表和标记表执行inner join。然后，在应用程序中，我可以过滤每个article_id的结果集，以获得给定文章的所有标签？后者似乎是一个相当冗长和低效的解决方案。

我是不是遗漏了什么？有没有一种规范的方法来表达单个查询？或者是单个查询加上少量的后处理？

除了SQL问题之外，Opaleye中的相应查询是什么样子的？也就是说，如果它能被翻译的话？

Answer 1

您通常会使用行限制查询来选择文章并按降序日期对它们进行排序，并使用具有聚合函数的连接或相关子查询来生成标记列表。

下面的查询给出了10篇最新的文章，以及数组中与它们相关的标签的名称：

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

您已经在your answer to your subsequent question中成功地将大部分GMB's answer转换为Opaleye。这是Opaleye的一个完整的工作版本。

欢迎您以后在Opaleye's issue tracker上提出这样的问题。你可能会在那里得到一个更快的响应。

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