Агрегация массивов по таблице ссылок в Opaleye

Я пытаюсь создать запрос Opaleye, соответствующий следующему SQL:

select * ,
    (select array_agg(tags.tagname)
     from articles_tags
     inner join tags on tags.id = articles_tags.tag_fk
         where articles_tags.article_fk = articles.id
    )
from articles

Используемые таблицы (упрощенные):

articles: (id, title, content)
articles_tags: (article_fk, tag_fk)
tags: (id, tagname)

Моя цель - запросить статьи, к которым прикреплен один или несколько тегов, и получить все прикрепленные теги в виде массива.

Пока что я получил следующие элементарные запросы:

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

-- | Query article-tag relations for the given articles.
taggedArticlesQ :: OE.SelectArr PA.ArticleIdField TaggedArticleR
taggedArticlesQ = proc articleId -> do
  ta <- allTaggedArticlesQ -< ()
  OE.restrict -< articleFk ta .=== articleId
  returnA -< ta

-- | Join article-ids and tag names for the given subset of articles.
articleTagNamesQ :: OE.SelectArr PA.ArticleIdField ArticleTagR
articleTagNamesQ = proc articleIds -> do
  ta <- taggedArticlesQ -< articleIds
  tags <- PT.allTagsQ -< ()
  OE.restrict -< PT.tagKey tags .=== tagFk ta
  returnA -< ArticleTag (articleFk ta) (PT.tagName tags)

Однако я не могу заставить агрегацию работать: следующее не проверяет тип, и я не понимаю, как составить эту агрегацию с помощью вышеуказанного запроса:

-- | Aggregate all tag names for all given articles
articleTagsQ :: PA.ArticleIdField -> OE.Select (PA.ArticleIdField, F (OE.SqlArray OE.SqlText))
articleTagsQ = OE.aggregate
          ( pArticleTag
              ArticleTag
                { atArticleFk = OE.groupBy,
                  atTagname = OE.arrayAgg
                }
          ) OE.selectTable articleTagNamesQ

В некоторых сообщениях в блогах и проблемах GitHub я обнаружил замечание о том, что агрегирование плохо работает с Product-Profunctors и Arrows и, следовательно, не может быть включено в запрос со стрелкой. Тем не менее, я относительно новичок в Haskell и не очень хорошо разбираюсь в теории, лежащей в основе этих двух библиотек (похоже, нет документации для начинающих); поэтому я не могу придумать общую структуру, как сочетать запросы с агрегацией. Есть несколько примеров Уильяма Яо здесь, но я не понимаю общую концепцию, поэтому я не могу применить эти примеры к моей проблеме.

Я был бы очень признателен, если бы кто-нибудь мог дать представление о том, как составлять агрегирование с регулярными запросами в Opaleye, спасибо!

Источник

2 ответа

Решение

После рассмотрения нескольких примеров, вот решение, которое мне наконец удалось создать и запустить:

import           Control.Arrow
import qualified Opaleye as OE
import qualified Data.Profunctor.Product as PP

type F field = OE.Field field 

-- | 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)
articleTagNamesQ = proc () -> do
  TaggedArticle {articleFk = aId, tagFk = tFk} <- allTaggedArticlesQ -< ()
  Tag {tagKey = tId, tagName = tn} <- allTagsQ -< ()
  OE.restrict -< tFk OE.(.===) tId -- INNER JOIN ON
  returnA -< (aId, tn)

-- | Aggregate all tag names for all articles
articleTagsQ :: OE.Select (F OE.SqlInt8, F (OE.SqlArray OE.SqlText))
articleTagsQ =
  OE.aggregate (PP.p2 (OE.groupBy, OE.arrayAgg)) $
    arr (first) <<< articleTagNamesQ

Ряд articles_tags таблица представлена ​​в Haskell полиморфным TaggedArticle* Тип Opaleye, и аналогично Tag* для строк тегов.

Ключевой момент - выбрать все строки двух таблиц, затем выполнить соединение и, наконец, выполнить агрегирование. Поскольку функция агрегирования в Opaleye не являетсяArrow ни ProductProfunctor, но OE.aggregate функция ожидает Select a, Мне не удалось включить агрегирование как часть запроса, написанного стрелками. Вместо этого мне пришлось написать отдельную функцию, которая принимаетSelect a как вход.

Обратите внимание, что агрегирование не может выполняться для более общих SelectArr. Из документации pacakge: "По замыслу нет функции агрегирования типаAggregator b b' -> \S.SelectArr a b -> S.SelectArr a b'. Такая функция позволила бы нарушить правила определения области действия SQL и привести к недействительным запросам ".

Мой код выше несколько упрощен. Я пробовал использовать для ключей полиморфные типы. Однако я не мог понять, как написать весь код в терминах этих оболочек newtype; вместо этого мне пришлось несколько раз разворачивать и перематывать поля.

Еще одна проблема, с которой я столкнулся, заключалась в определении типа строки, являющегося результатом JOIN. Изначально я определил новый полиморфный тип строки. Но тогда мне не удалось должным образом развернуть поля этого типа, чтобы я мог передать их вOE.Aggregator. Поэтому я остановился на более подробной нотации кортежей выше.

Источник

Я внес несколько изменений в код в вашем ответе, чтобы он компилировался как отдельный файл:

  • Оператор должен быть OE..=== скорее, чем OE.(.===)
  • В arr first нужно было удалить
  • Я добавил несколько определений типов данных, определений таблиц и расширений
{-# LANGUAGE Arrows #-}
{-# LANGUAGE FlexibleInstances #-}
{-# LANGUAGE MultiParamTypeClasses #-}
{-# LANGUAGE TemplateHaskell #-}

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

type F field = OE.Field field

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

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)
articleTagNamesQ = proc () -> do
  TaggedArticle {articleFk = aId, tagFk = tFk} <- allTaggedArticlesQ -< ()
  Tag {tagKey = tId, tagName = tn} <- allTagsQ -< ()
  OE.restrict -< tFk OE..=== tId -- INNER JOIN ON
  returnA -< (aId, tn)

-- | Aggregate all tag names for all articles
articleTagsQ :: OE.Select (F OE.SqlInt8, F (OE.SqlArray OE.SqlText))
articleTagsQ =
  OE.aggregate (PP.p2 (OE.groupBy, OE.arrayAgg)) articleTagNamesQ
Источник
Другие вопросы по тегам