跨Opaleye中的链接表进行数组聚合 [英] Array-aggregation across a link-table in Opaleye

问题描述

我正在尝试构建一个与以下SQL匹配的Opaleye查询:

I'm trying to construct an Opaleye query that matches the following 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)

我的目标是查询附加了一个或多个标签的文章，并以数组的形式检索所有附加的标签.

My goal is to query for articles that have one or more tags attached, and retrieve all attached tags as an array.

到目前为止，我得到了以下基本查询:

So far, I got the following elementary queries:

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

但是，我无法使聚合工作:以下代码不进行类型检查，并且我不理解如何通过上述查询来构成此聚合:

However, I cannot get the aggregation to work: The following does not type-check, and I do not understand how to compose this aggregation with the above query:

-- | 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还是比较陌生，还没有真正理解这两个库背后的理论(似乎没有对初学者友好的文档).因此，我无法提出如何将查询与聚合结合的一般结构. William Yao有一些示例这里，但是我不了解一般概念，因此无法将这些示例应用于我的问题.

In some blog posts and GitHub issues, I found a remark that the aggregation does not play nice with Product-Profunctors and Arrows and, therefore, cannot be included in an arrow query. Yet, I am relatively new to Haskell and haven't really understood the theory behind these two libraries (there does not seem to be a beginner-friendly documentation); therefore, I cannot come up with the general structure how to combine queries with aggregation. There are some examples by William Yao here, but I don't understand the general concept, so I can't apply these examples to my problem.

如果有人可以提供关于如何在Opaleye中使用常规查询进行聚合的见解，我将非常感激，谢谢！

I would highly appreciate if someone can provide insight on how to compose aggregation with regular queries in Opaleye, thanks!

推荐答案

研究了几个示例之后，下面是我最终设法构建并运行的解决方案:

After crunching through several examples, here is the solution I finally managed to build and run:

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

在Haskell中，articles_tags表的一行由多态的TaggedArticle* Opaleye类型表示，对于标记行，类似地为Tag*.

A row of the articles_tags table is represented in Haskell by the polymorphic TaggedArticle* Opaleye type, and similarly Tag* for tag rows.

关键点是选择两个表的所有行，然后执行联接，最后进行汇总.因为Opaleye中的聚合函数既不是Arrow也不是ProductProfunctor，但是OE.aggregate函数期望的是Select a，所以我无法将聚合作为用箭头表示的查询的一部分包括在内.相反，我不得不编写一个单独的函数，该函数以Select a作为输入.

The key point is to select all rows of the two tables, then perform the join, and then finally do the aggregation. Because the aggregation function in Opaleye is neither an Arrow nor a ProductProfunctor, but the OE.aggregate function expects a Select a, I could not include the aggregation as part of a query written in arrow notation. Instead, I had to write a separate function that takes a Select a as input.

请注意，无法在更通用的SelectArr上执行聚合.从pacakge文档中:根据设计，没有类型为Aggregator b b' -> \S.SelectArr a b -> S.SelectArr a b'的聚合函数.这样的功能将允许违反SQL的范围规则，并导致无效的查询."

Note that aggregation cannot be performed on the more general SelectArr. From the pacakge documentation: "By design there is no aggregation function of type Aggregator b b' -> \S.SelectArr a b -> S.SelectArr a b' . Such a function would allow violation of SQL's scoping rules and lead to invalid queries."

我上面的代码有些简化.我试图对键使用多态类型.但是，我无法弄清楚如何用这些新型包装器来编写所有代码.取而代之的是，我不得不拆开并重新包裹田地.

My code above is somewhat simplified. I tried to use polymorphic types for keys. However, I could not figure out how to write all code in terms of these newtype wrappers; instead, I had to unwrap and rewrap the fields several times.

我遇到的另一个问题是JOIN导致的行类型的定义.最初，我定义了一个新的多态行类型.但是后来我没有设法正确解开该类型的字段，因此我可以将它们输入OE.Aggregator中.因此，我选择了上面更详细的元组符号.

Another issue I ran into was the definition of the row type that results from the JOIN. Initially, I defined a new polymorphic row type. But then I did not manage to properly unwrap the fields of that type so I could feed them into the OE.Aggregator. Therefore, I settled for the more verbose tuple notation above.

这篇关于跨Opaleye中的链接表进行数组聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！