Skip to content

进阶查询技巧

database.get() 已经能实现一些简单的查询了。然而在实际的开发中,我们通常会遇到排序、分组乃至聚合等更复杂的查询需求。此时就轮到更加强大的 database.select() 方法登场了。

基本用法

database.select() 会创建一个 Selection 对象。它提供了一系列的链式方法,你可以将其理解成一个查询语句的构造器。构造完成后,你可以调用 .execute() 方法来执行最终的查询。下面是一个简单的例子:

ts
ctx.database.get('foo', { id: { $gt: 5 } })
// 等价于
ctx.database
  .select('foo')
  .where(row => $.gt(row.id, 5))
  .execute()

排序与分页

使用 .orderBy() 方法来对查询结果排序,使用 .limit().offset() 方法来分页:

ts
// 按 id 降序排列,从第 100 条开始取 10 条数据
ctx.database
  .select('foo')
  .orderBy('id', 'desc')
  .limit(10)
  .offset(100)
  .execute()

求值表达式

.orderBy().where() 等方法都支持传入一个函数,这个函数会接受一个 row 参数,表示当前正在处理的数据行。你可以在这个函数中返回一个值,这个值会被用于排序或筛选。

ts
// 返回 id 大于 5 的数据行,并按 id 升序排列
ctx.database
  .select('foo')
  .where(row => $.gt(row.id, 5))
  .orderBy(row => row.id)
  .execute()

字段映射

.project() 方法可以用于映射查询结果。它接受一个对象,对象的键表示要映射的字段名,值表示映射的表达式。下面是一个例子:

ts
// 返回的数组元素将只含有 a, b 属性
ctx.database
  .select('foo')
  .project({
    a: row => $.add(row.id, 1),         // a = id + 1
    b: row => $.multiply(row.id, 2),    // b = id * 2
  })
  .execute()

聚合查询

.execute() 也可以传入一个带有聚合运算的求值函数。如果你这样做,此时返回的结果将不再是一个数组,而是该表达式计算出的值。

ts
// 返回 id 大于 5 的数据行的数量
ctx.database
  .select('foo')
  .where(row => $.gt(row.id, 5))
  .execute(row => $.count(row.id))

除了 .count() 外还有其他的一些聚合运算,例如 $.sum()$.max() 等。聚合运算与其他求值函数的区别在于,聚合运算的外部不能再包含 row 的引用。

此外,只有特定方法中才能使用聚合运算,例如 .execute().having() 等。

分组查询

.groupBy().having() 方法可以用于分组查询。.groupBy() 方法接受一个字段名或求值函数,.having() 方法接受一个含有聚合运算并返回布尔值的表达式。下面是一个例子:

ts
// 按照 value 字段分组,返回结果数大于 5 的分组
ctx.database
  .select('foo')
  .groupBy('value')
  .having(row => $.gt($.count(row.id), 5))
  .execute()

.groupBy() 可以接受一个数组,表示同时对数组中的字段进行分组。甚至也可以是一个对象,与 .project() 中的用法类似。

ts
// 返回的数据将按照 id - value 的值分组
ctx.database
  .select('foo')
  .groupBy({
    key: row => $.subtract(row.id, row.value),
  })
  .execute()

.having() 中可以使用的 row 属性仅限于 .groupBy() 中的字段。

添加字段

.groupBy() 还额外接受一个二参数,用于在查询结果中添加聚合字段。这个参数是一个对象,同样与 .project() 中的用法类似。下面是一个例子:

ts
// 返回的数据包含 value, sum, count 三个属性
ctx.database
  .select('foo')
  .groupBy('value', {
    sum: row => $.sum(row.id),
    count: row => $.count(row.id),
  })
  .execute()

多级分组

可以通过链式调用 .groupBy() 方法来实现多级分组。下面是一个例子:

ts
ctx.database
  .select('foo')
  .groupBy(['uid', 'pid'], {
    submit: row => $.sum(1),
    accept: row => $.sum(row.value),
  })
  .groupBy(['uid'], {
    submit: row => $.sum(row.submit),
    accept: row => $.sum($.if($.gt(row.accept, 0), 1, 0)),
  })
  .orderBy('uid')
  .execute()

连接查询 实验性

最后介绍一下连接查询的用法。使用 .join() 可以将多个表连接起来,返回一个新的 Selection,其属性分别对应多个表的名称。下面是一个例子:

ts
// 返回的数据包含 foo, bar 两个属性
ctx.database
  .join(['foo', 'bar'], (foo, bar) => $.eq(foo.id, bar.id))
  .orderBy('foo.id') // orderBy 可以使用 'a.b' 的形式
  .execute()

如果你的表名比较复杂,你也可以为参与连接的各个表指定别名。将用于指定表名的数组改为传入一个对象,并修改传入函数的参数即可。下面是一个例子:

ts
// 返回的数据包含 t1, t2 两个属性
ctx.database
  .join({ t1: 'foo', t2: 'bar' }, row => $.eq(row.t1.id, row.t2.id))
  .orderBy('t1.id') // orderBy 可以使用 'a.b' 的形式
  .execute()