Запрос всех N элементов в отношении M: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 DSL? То есть можно ли его вообще перевести?

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

Вы успешно преобразовали большую часть ответа GMB на Opaleye в своем ответе на следующий вопрос. Вот полностью рабочая версия в Opaleye.

В будущем вы можете задавать такие вопросы в системе отслеживания проблем Opaleye. Вы, вероятно, получите там более быстрый ответ.

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