[RFC] Query Builder Roadmap

[shigma]

尽管目前的 ORM API 已经覆盖了大部分使用场景，但仍然有一些不被支持的行为。为此，我们需要引入更强大的 Query Builder 来实现更多的数据库操作。此提案为 Koishi 补全了常见数据库 select 特性的支持，包括下列特性：

• group by 子句和 having 子句
• join 运算符和 on 子句
• order by 子句和 select 子句后允许出现表达式
• 子查询 (包括 select 型子查询，where 型子查询，from 型子查询等)

在实现过程中需要满足以下要求：

1. 完全的类型支持，所有定义的属性都能正确提示类型，所有的 API 都能正确推导返回值，包括子查询和连接场景中含有跨表引用和别名的情况。
2. 表达式的构建允许提供 helper (也可以像现在一样不提供)，但最终的表达式结果必须做到可序列化 (Date 等原生类型的序列化问题不在考虑范畴)。
3. 所有实现的特性都需要对目前官方的所有数据库提供支持 (memory, level, mongo, sqlite, mysql)。

由于这个题目比较复杂，可能会分为多个阶段完成。

• Basic Support
• Sort Callback
• Field Callback
• Group Operation
• Subquery
• Join Operation

---

Selection

Selection 表达了一种类似 Table 的数据结构，也是大部分操作的基础。除了可以通过 database.select() 创建外，你还会在 group 和 join 等操作中遇到它。

const selection = database.select(name, query?)
selection.orderBy(field, order?)
selection.limit(limit).offset(offset)
selection.project(fields)
selection.execute()

下面是一些典型 SQL 的对应写法：

// select * from user
database
  .select('user')
  .execute()

// select * from user where id = 1
database
  .select('user', { id: 1 })
  .execute()

// select * from user limit 10 offset 10
database
  .select('user')
  .limit(10)
  .offset(10)
  .execute()

// select * from user order by money desc
// 这里的 desc 通过 orderBy 的第二个参数表达
database
  .select('user')
  .orderBy('money', true)
  .execute()

// select id, name from user
database
  .select('user')
  .project(['id', 'name'])
  .execute()

Evaluation Callback

Evaluation Callback 允许通过回调函数的方式定义请求中的表达式。你可以在 project 和 orderBy 等方法中使用这种语法。函数相比对象不仅更接近我们熟悉的书写风格，而且能提供更好的类型提示。

import { $ } from 'koishi'

// select * from user order by money + savings
database
  .select('user')
  .orderBy(row => $.add(row.money, row.savings))
  .execute()

// select id as foo, money + savings as bar from user
database
  .select('user')
  .project({
    foo: 'id',
    bar: row => $.add(row.money, row.savings),
  })
  .execute()

Group Operation

// select * from user group by foo
database
  .select('user')
  .groupBy('foo')
  .execute()

// select * from user group by foo, bar
database
  .select('user')
  .groupBy(['foo', 'bar'])
  .execute()

// select * from user group by foo + bar
database
  .select('user')
  .groupBy(row => $.add(row.foo, row.bar))
  .execute()

// select foo, sum(bar) as bar from user group by foo having count(id) > 1
database
  .select('user')
  .groupBy('foo', row => $.gt($.count(row.id), 1))
  .project({
    foo: 'foo',
    bar: row => $.sum(row.bar),
  })
  .execute()

Subquery

子查询可能出现在多个位置，不过它们的语法是类似的。

// 获取用户的 money 排行
// select (select count(*) from user where money > user1.money) as rank from user user1
database
  .select('user')
  .project({
    rank: u1 => database
      .select('user', u2 => $.gt(u2.money, u1.money))
      .evaluate(u2 => $.count(u2.id)),
  })
  .execute()

以上是子查询出现在 select 后的情况。除此以外还有出现在 from 和 where 中的情况：

// select * from user where foo in (select ...)
database
  .select('user', {
    foo: database.select(...),
  })
  .execute()

// select * from (select ...) as bar
const selectionA = database.select(...)
const selectionB = database
  .select(selectionA)
  .execute()

Join Operation

const selectionA = database.select('foo')
const selectionB = database.select('bar')

// select * from foo inner join bar
database.select([selectionA, selectionB])

// select * from foo left join bar
database.select([selectionA, selectionB], [true, false])

// select * from foo right join bar
database.select([selectionA, selectionB], [false, true])

// select * from foo full join bar
database.select([selectionA, selectionB], [true, true])

// select * from foo inner join bar on foo.qux = bar.qux
database.select([selectionA, selectionB], (foo, bar) => $.eq(foo.qux, bar.qux))

[shigma]

Additional notes of groupBy

Group fields

The first parameter can be a single named field, an array of named fields, or a dict of field expressions.

// select foo from user group by foo
database
  .select('user')
  .groupBy('foo')
  .execute()

// select foo, bar from user group by foo, bar
database
  .select('user')
  .groupBy(['foo', 'bar'])
  .execute()

// select foo + bar as qux from user group by qux
database
  .select('user')
  .groupBy({
    qux: row => $.add(row.foo, row.bar)),
  })
  .execute()

Aggregations

The second parameter can be used to add additional aggregation fields.

// select foo, sum(bar) as bar, max(qux) as qux from user group by foo
database
  .select('user')
  .groupBy('foo', {
    bar: row => $.sum(row.bar),
    qux: row => $.max(row.qux),
  })

And the third parameter is used for having clause. Both parameters can be omitted.

// select foo from user group by foo having count(id) > 1
database
  .select('user')
  .groupBy('foo', row => $.gt($.count(row.id), 1))
  .execute()

Chaining

groupBy can be chained multiple times. Thanks to @undefined-moe for the real-world example.

database
  .select('user')
  .groupBy(['domain', 'pid', 'uid'], {
    submit: row => $.sum(1),
    accept: row => $.sum($.if($.eq(row.status, 'accept'), 1, 0)),
  })
  .groupBy(['domain', 'uid'], {
    submit: row => $.sum(row.submit),
    accept: row => $.sum($.min(row.accept, 1)),
  })