服务器端使用over表计算表列

Question

假设db中有两个表：

表registries

       Column       |            Type             |                           
--------------------+-----------------------------+---------
 registry_id        | integer                     | not null 
 name               | character varying           | not null
 ...
 uploaded_at        | timestamp without time zone | not null

表rows

    Column     |            Type             | Modifiers 
---------------+-----------------------------+-----------
 row_id        | character varying           | not null
 registry_id   | integer                     | not null
 row           | character varying           | not null

在现实世界中，registries只是一个csv文件，而rows是文件的行。在我的scala巧妙的应用程序中，我想知道每个文件中有多少行。

registries

1,foo,...
2,bar,...
3,baz,...

rows

aaa,1,...
bbb,1,...
ccc,2,...

预期结果：

1,foo,... - 2
2,bar,... - 1
3,baz,... - 0

我现在的代码是(slick3.0)：

def getRegistryWithLength(rId: Int) = {
    val q1 = registries.filter(_.registryId===rId).take(1).result.headOption
    val q2 = rows.filter(_.registryId===rId).length.result
    val registry = Await.result(db.run(q1), 5.seconds)
    val length = Await.result(db.run(q2), 5.seconds)
    (registry, length)
}

(Await是个坏主意，我知道)

如何使用单个getRegistryWithLength查询执行sql？

我可以将列row_n添加到表registries中，但是在删除/插入rows表的查询之后，我将被迫更新列row_n。

如何在db服务器端的表row_n中进行自动计算列registries？

Answer 1

基本查询可以是：

SELECT r.*, COALESCE(n.ct, 0) AS ct
FROM   registry r
LEFT   JOIN (
   SELECT registry_id, count(*) AS ct
   FROM   rows
   GROUP  BY registry_id
   ) n USING (registry_id);

LEFT [OUTER] JOIN是必不可少的，因此您不会在rows中没有相关行的情况下从注册表中筛选行。

如果没有找到相关行，则COALESCE返回0而不是NULL。

关于这个问题有很多相关的答案。其中一个在这里：

• SQL:如何在sql查询中保存顺序？

为了方便起见，您可以将其包装在VIEW中：

CREATE VIEW reg_rn AS
SELECT ...

..。你会像查表一样查询。

旁白:使用保留SQL关键字作为标识符是不明智的。row对列名是不允许的(即使在Postgres中允许)。

Answer 2

感谢埃尔文·勃兰特给出了很棒的答案，使用它，我为我的scala巧妙的应用程序编写了代码。

Scala代码看起来比普通sql复杂得多：

val registryQuery = registries.filter(_.userId === userId)
val rowQuery = rows groupBy(_.registryId) map { case (regId, rowItems) => (regId, rowItems.length)}
val q = registryQuery joinLeft rowQuery on (_.registryId === _._1) map {
  case (registry, rowsCnt) => (registry, rowsCnt.map(_._2))
}

但很管用！