在Scala框架中支持PostgreSQL特定的array_agg函数吗? [英] Support of PostgreSQL specific array_agg function in scala frameworks?
问题描述
是否存在一些类似scala的关系数据库框架(anorm，squeryl等)，使用类似postgres的聚合器在分组依据之后生成列表，或者至少模拟了其用法?
Is there some scala relational database framework (anorm, squeryl, etc...) using postgres-like aggregators to produce lists after a group-by, or at least simulating its use?
我希望有两个实施级别:
I would expect two levels of implementation:
-
一种标准"代码，其中至少将与array_agg进行任何SQL分组转换为正在聚合的类型的列表
a "standard" one, where at least any SQL grouping with array_agg is translated to a List of the type which is being aggregated,
和标量ORM驱动的"，其中允许某种类型的联接，以便如果聚合是其他表的外键，则将生成另一个表的元素列表.当然，这最后一步是SQL所无法实现的，但是，如果我使用的是更强大的语言，那么我不介意使用某些类固醇.
and a "scala ORM powered" one where some type of join is allowed so that if the aggregation is a foreign key to other table, a List of elements of the other table is produced. Of course this last thing is beyond the reach of SQL, but if I am using a more powerful language, I do not mind some steroids.
我特别感兴趣的是，slick的文档正是基于scala的group-by表示法，它似乎明确地否定了group-by结果的列表输出.
I find specially intriguing that the documentation of slick, which is based precisely in allowing scala group-by notation, seems to negate explicitly the output of lists as a result of the group-by.
您有一个典型的多对多表，例如，产品和供应商，对(p_id，s_id).您要为每种产品生成供应商列表.所以postgresql查询应该是
You have the typical many-to-many table of, say, products and suppliers, pairs (p_id, s_id). You want to produce a list of suppliers for each product. So the postgresql query should be
SELECT p_id, array_agg(s_id) from t1 group by p_id
一个人可能会期待一些惯用的方式来实现此目的，但我不知道如何做到.此外，如果我们去一些ORM，那么我们还可以考虑分别与产品和供应商表的连接，分别位于p_id和s_id上，并作为答案得到包含对象和对象的zip(产品，(供应商1，供应商2，供应商N)).不仅是ids
One could expect some idiomatic way to to this in slick, but I do not see how. Furthermore, if we go to some ORM, then we could also consider the join with the tables products and suppliers, on p_id and s_id respectively, and get as answer a zip (product, (supplier1, supplier2, supplierN)) containing the objects and not only the ids
推荐答案
我也不确定我是否理解您提出的问题，能否详细说明?
I am also not sure if I understand you question correct, could you elaborate?
在slick中，您当前不能将postgres"array_agg"或"string_agg"用作Query类型的方法.如果要使用此特定功能，则需要使用自定义sql.但是:我前段时间添加了一个问题( https://github.com/slick/slick/issues/923 ，您应该遵循此讨论)，我们已经准备好了cvogt的原型.
In slick you currently can not use postgres "array_agg" or "string_agg" as a method on type Query. If you want to use this specific function then you need to use custom sql. But: I added an issue some time ago (https://github.com/slick/slick/issues/923, you should follow this discussion) and we have a prototype from cvogt ready for this.
我过去需要使用"string_agg"并为其添加了一个补丁(请参见 https://github.com/mobiworx/slick/commit/486c39a7ed90c9ccac356dfdb0e5dc5c24e32d63 )，所以这可能对您有所帮助.查看"AggregateTest"以了解更多信息.
I needed to use "string_agg" in the past and added a patch for it (see https://github.com/mobiworx/slick/commit/486c39a7ed90c9ccac356dfdb0e5dc5c24e32d63), so maybe this is helpful to you. Look at "AggregateTest" to learn more about it.
另一种可能性是将"array_agg"的用法封装在数据库视图中，然后将此视图与slick一起使用.这样，您就无需直接使用"array_agg".
Another possibility is to encapsulate the usage of "array_agg" in a database view and just use this view with slick. This way you do not need "array_agg" directly in slick.
这篇关于在Scala框架中支持PostgreSQL特定的array_agg函数吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
