七、深入理解Waterline的多段查询（Query）

Waterline 查询

查询分类

Waterline查询分两种，一种用来查找数据库记录，最典型的是find和findOne，其中：

• find：返回满足条件的所有数据，支持分页查询和排序，查询的结果是数组。
• findOne：返回满足条件的特定数据，查询的结果是数据库里面的一行。
• findOrCreate：查询满足条件的特定记录，如果不存在就用初始值创建该记录。

另一种是统计查询，有三个函数：

• avg：统计满足条件的指定字段的平均值。
• sum：满足条件的指定字段的总和。
• count：满足条件的记录数量。

Criteria 查询条件

Waterline 把查询条件封装成类似JQL（javascript query language）的规范。这样在使用Waterline的时候，就可以完全不需要关注数据库的查询语法，并且通过不同的数据库适配器，把这种查询规范解析成对应数据库系统的SQL语句，从而实现可以在任意数据库之间随意切换（Migrate）的目的。
不论是哪种查询函数，都需要指定查询条件，相当于SQL语句里面的Where。比如：

var usersNamedFinn = await User.find({name:'Finn'});

经过解析，最终执行这样的SQL语句（以MySql数据库为例）：

select * from `user` where name='Finn'

在这个查询中，find的参数{name:‘Finn’} 就是查询条件。这里被解析成name=‘Finn’

Key pairs 键值对

Criteria 查询条件的基本形式是键值对，比如上面的name:‘Finn’，这种Key/Value pairs是严格匹配查询，相当于SQL语句中的相等，可以同时进行多种属性查询，多个属性之间默认是逻辑与( and )的关系，比如：

await User.find({name:'Finn',age:18});

以上语句编译后的SQL是这样的：

select * from `user` Where name='Finn' and age=18

Or 谓语 (predicate)

多个查询条件如果需要或关系（ or ）需要使用 “or” 查询谓语。比如：

await User.find({
	email: "zzcai@gmail.com",
    or: [
        {
            id: {'<':30}
        },
        {
           id: {'>':900}
        }
    ]
});

以上语句编译后的SQL是这样的：

select * from `user` Where `email` = 'zzcai@gmail.com' and (`id` < 30 or `id` > 900)

条件修饰符 Criteria modifiers

Waterline 可用的修饰符有10个，分别是：

'<' 
'<='
'>'
'>='
'!='
nin
in
contains
startsWith
endsWith

使用示例如下：

Model.find({
  age: { '<': 30 }
});
//相当于where age<30
var musicCourses = await Course.find({
  subject: { contains: 'music' }
});
//相当于 where subject like '%music%'

查询选项 Query options

Waterline 查询选项包含：

• limit 查询最多返回记录数（相当pageSize）
• skip 查询跳过记录数（相当于page * pageSize）
• sort 排序
  如果查询的时候，需要分页，排序等查询选项的时候，需要重新组织查询条件，把前面相当于where的条件写到where键里面，比如：

Model.find({ where: { name: 'foo' }, limit: 10, skip: 10 });
//相当于SQL:select * from `Model` where name='foo' limt 10,10

指定字段 Select 和排除字段 Omit

可以通过指定select或omit来控制要查询的字段，比如

// 假设User的所有字段如下：email,age,id 
User.findOne({
  omit: ['email'],
  where: {
     id:1
  },
  skip: 90,
  sort: 'name asc'
})
// 本查询相当于SQL:select age,id from `user` where id=1
User.findOne({
  select: ['email'],
  where: {
     id:1
  },
  skip: 90,
  sort: 'name asc'
})
// 本查询相当于SQL:select email from `user` where id=1

Waterline 中select和omit同时有内容的时候，select必须是[“*”] 另外有一些自动产生的字段，比如id不能排除

Waterline 怎么工作

一个完整的Waterline query一共分为五个阶段，为了更好的理解，我们在本博文内先探讨简单的单表查询（多表关联查询将放在后面专门探讨）。这样我们可以把查询分成更简单一点的三阶段。

Stage 1 query

创建查询实例以及一些初步的验证。可以直接用我们再models里面定义好的数据模型，根据Sails的查询规则创建模型查询实例，比如一下最简单的实例：

var q = User.findOne({ id: 153});

在Waterline 库里面，有个methods/find-one.js，会组织成一个query,这个query包含三个内容method,using,criteria 比如上面的查询会转换成：

{
  method: 'findOne', // << 方法名称
  using: 'user', // << 对应的数据模型名称
  // 如果方法名称是 "find"/"findOne", "update", "destroy", "count", "sum", or "avg"等就会
  // 根据方法里面的参数形成查询条件字典。
  criteria: {
	id:153
  }
}

Stage 2 query

以上第一阶段产生的查询内容，通过一个forgeStageTwoQuery查询函数进一步解析成这样：

{
  method: 'findOne',
  using: 'user',
  criteria: {
    where: { id: 153 },
    limit: 2,//因为是findOne，这个limit默认被设定成2
    skip: 0,
    sort: [],
    select: [ '*' ],//我们没有指定select，默认值为*
    omit: []
  },
  populates: {} //这个是关联表
}

Stage 3 query

第三个阶段也叫“native query”：第二阶段的内容，通过waterline-sql-builder库 SQLBuilder，转换成真正的SQL查询语句：

select `createdAt`, `updatedAt`, `id`, `email`, `password`, `nickname`, `age` from `user` where `id` = ? limit ?
//Bindings: 153,2

这里把Waterline的工作步骤做了简化，实际情况比这个还要复杂一些。

三段查询的应用

了解了Waterline的工作原理，我们就可以自己利用Waterline来生成我们想要的SQL查询语句，从而发送给存储过程（因为我们需要使用存储过程操作数据库），这样可以省掉我们自己对复杂查询条件的解析，避免重复造轮子的工作，同时也能够使得我们对Sails的改造最小化，改造后的代码与Sails的兼容性最好。
要实现把符合Waterline要求的查询字典（类似JQL语法）转换成SQL，需要用到上面提到的forgeStageTwoQuery和SQLBuilder。

forgeStageTwoQuery

forgeStageTwoQuery 具体文件是waterline库的 utils/query/private/forgeStageTwoQuery.js，在waterline是私有函数，并没有直接被export出来，我们自然是可以修改waterline库的源代码，但是一旦修改依赖库的源代码，除非我们提交给源代码管理方并成功合并我们的request，否则日后依赖库升级了，我们就可能会因为我们的分叉而无法升级依赖库的新功能。这不是个好办法。建议的做法是把改文件以及改文件关联的依赖文件复制到我们自己的源代码里面。
在本项目里面，我们在utils子文件夹里面创建waterlineSource文件夹，把waterline里面我们需要用到的文件copy到该文件夹。
在wlBase.ts （关于wlBase.ts可见前面博文描述，或参考github上源代码）里面添加代码如下：

const forgeStageTwoQuery = require('utils/waterlineSource/query/forge-stage-two-query');
/**
   * 调用waterline 查询第二阶段的方法，把sails查询条件转换成可以转sql语句的对象
   * @param tbName 表名称
   * @param method 要转换的方法
   * @param criteria 查询条件
   * @returns 返回可以转sql语句的对象
   */
  protected getSqlCriteria(tbName: string, method: string, criteria: any): any {
    delete criteria.select;
    delete criteria.omit;
    let query = { method: method, using: tbName, criteria: criteria }

    forgeStageTwoQuery(query, this.sa.models[tbName].waterline);
    return query;
  }
  //调用示例 getSqlCriteria('user','findOne',{id:153})

SQLBuilder

由于waterline-sql-builder库是Waterline的依赖库，Sails既然安装了Waterline，就必然已经安装这个库，我们是可以直接用的，使用方法也非常简单：

var SQLBuilder = require('waterline-sql-builder')({
        dialect: 'my-sql'
});
//这个地方有个数据库方言的参数 dialet:'my-sql'，目前的做法是'硬编码'
//如果有需要，可以通过解析config/datastores.js里面设置的适配器来解析，具体代码在源代码的utils/wlBase.ts里面有

导入SQLBuilder之后，要把第二阶段给定的query转换成SQL，可以这样做：

var query=
  {
    from: 'user'
    where: { id: 153 },
    limit: 2,
    skip: 0,
    sort: [],
    select: [ '*' ],
    omit: []
  }
let sql=SQLBuilder.generate(query);
//生成结果：{ sql: 'select * from `user`	where `id` = ? limit ?', bindings: [ 153, 2 ] }