Querying - 查询 - Where - 《Sequelize ORM v5 中文文档》

Where

Where

无论你是通过 findAll/find 或批量 updates/destroys 进行查询,都可以传递一个 where 对象来过滤查询.

where 通常用 attribute:value 键值对获取一个对象,其中 value 可以是匹配等式的数据或其他运算符的键值对象.

也可以通过嵌套 or 和 and 运算符 的集合来生成复杂的 AND/OR 条件.

基础

const Op = Sequelize.Op;
Post.findAll({
  where: {
    authorId: 2
  }
});
// SELECT * FROM post WHERE authorId = 2
Post.findAll({
  where: {
    authorId: 12,
    status: 'active'
  }
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';
Post.findAll({
  where: {
    [Op.or]: [{authorId: 12}, {authorId: 13}]
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
Post.findAll({
  where: {
    authorId: {
      [Op.or]: [12, 13]
    }
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
Post.destroy({
  where: {
    status: 'inactive'
  }
});
// DELETE FROM post WHERE status = 'inactive';
Post.update({
  updatedAt: null,
}, {
  where: {
    deletedAt: {
      [Op.ne]: null
    }
  }
});
// UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;
Post.findAll({
  where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
});
// SELECT * FROM post WHERE char_length(status) = 6;

操作符

Sequelize 可用于创建更复杂比较的符号运算符 -

const Op = Sequelize.Op
[Op.and]: [{a: 5}, {b: 6}] // (a = 5) 且 (b = 6)
[Op.or]: [{a: 5}, {a: 6}]  // (a = 5 或 a = 6)
[Op.gt]: 6,                // id > 6
[Op.gte]: 6,               // id >= 6
[Op.lt]: 10,               // id < 10
[Op.lte]: 10,              // id <= 10
[Op.ne]: 20,               // id != 20
[Op.eq]: 3,                // = 3
[Op.is]: null              // 为 NULL
[Op.not]: true,            // 不是 TRUE
[Op.between]: [6, 10],     // 在 6 和 10 之间
[Op.notBetween]: [11, 15], // 不在 11 和 15 之间
[Op.in]: [1, 2],           // 在 [1, 2] 之中
[Op.notIn]: [1, 2],        // 不在 [1, 2] 之中
[Op.like]: '%hat',         // 包含 '%hat'
[Op.notLike]: '%hat'       // 不包含 '%hat'
[Op.iLike]: '%hat'         // 包含 '%hat' (不区分大小写)  (仅限 PG)
[Op.notILike]: '%hat'      // 不包含 '%hat'  (仅限 PG)
[Op.startsWith]: 'hat'     // 类似 'hat%'
[Op.endsWith]: 'hat'       // 类似 '%hat'
[Op.substring]: 'hat'      // 类似 '%hat%'
[Op.regexp]: '^[h|a|t]'    // 匹配正则表达式/~ '^[h|a|t]' (仅限 MySQL/PG)
[Op.notRegexp]: '^[h|a|t]' // 不匹配正则表达式/!~ '^[h|a|t]' (仅限 MySQL/PG)
[Op.iRegexp]: '^[h|a|t]'    // ~* '^[h|a|t]' (仅限 PG)
[Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (仅限 PG)
[Op.like]: { [Op.any]: ['cat', 'hat']} // 包含任何数组['cat', 'hat'] - 同样适用于 iLike 和 notLike
[Op.overlap]: [1, 2]       // && [1, 2] (PG数组重叠运算符)
[Op.contains]: [1, 2]      // @> [1, 2] (PG数组包含运算符)
[Op.contained]: [1, 2]     // <@ [1, 2] (PG数组包含于运算符)
[Op.any]: [2,3]            // 任何数组[2, 3]::INTEGER (仅限PG)
[Op.col]: 'user.organization_id' // = 'user'.'organization_id', 使用数据库语言特定的列标识符, 本例使用 PG
[Op.gt]: { [Op.all]: literal('SELECT 1') }     // > ALL (SELECT 1)

范围选项

所有操作符都支持范围类型查询.

请记住,提供的范围值也可以定义绑定的 inclusion/exclusion.

// 所有上述相等和不相等的操作符加上以下内容:
[Op.contains]: 2           // @> '2'::integer (PG range contains element operator)
[Op.contains]: [1, 2]      // @> [1, 2) (PG range contains range operator)
[Op.contained]: [1, 2]     // <@ [1, 2) (PG range is contained by operator)
[Op.overlap]: [1, 2]       // && [1, 2) (PG range overlap (have points in common) operator)
[Op.adjacent]: [1, 2]      // -|- [1, 2) (PG range is adjacent to operator)
[Op.strictLeft]: [1, 2]    // << [1, 2) (PG range strictly left of operator)
[Op.strictRight]: [1, 2]   // >> [1, 2) (PG range strictly right of operator)
[Op.noExtendRight]: [1, 2] // &< [1, 2) (PG range does not extend to the right of operator)
[Op.noExtendLeft]: [1, 2]  // &> [1, 2) (PG range does not extend to the left of operator)

组合

{
  rank: {
    [Op.or]: {
      [Op.lt]: 1000,
      [Op.eq]: null
    }
  }
}
// rank < 1000 OR rank IS NULL
{
  createdAt: {
    [Op.lt]: new Date(),
    [Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
  }
}
// createdAt < [timestamp] AND createdAt > [timestamp]
{
  [Op.or]: [
    {
      title: {
        [Op.like]: 'Boat%'
      }
    },
    {
      description: {
        [Op.like]: '%boat%'
      }
    }
  ]
}
// title LIKE 'Boat%' OR description LIKE '%boat%'

运算符别名

Sequelize 允许将特定字符串设置为操作符的别名.使用v5,将为你提供弃用警告.

const Op = Sequelize.Op;
const operatorsAliases = {
  $gt: Op.gt
}
const connection = new Sequelize(db, user, pass, { operatorsAliases })
[Op.gt]: 6 // > 6
$gt: 6 // 等同于使用 Op.gt (> 6)

运算符安全性

默认情况下,Sequelize 将使用 Symbol 运算符. 使用没有任何别名的 Sequelize 可以提高安全性.没有任何字符串别名将使得运算符可能被注入的可能性降到极低,但你应该始终正确验证和清理用户输入.

一些框架会自动将用户输入解析为js对象,如果你无法清理输入,则可能会将带有字符串运算符的 Object 注入Sequelize.

为了更好的安全性,强烈建议在代码中使用 Sequelize.Op 中的符号运算符,如Op.and / Op.or,而不依赖于任何基于字符串的运算符,如 $and / $or. 你可以通过设置 operatorsAliases 参数来限制应用程序所需的别名,记住清理用户输入,特别是当你直接将它们传递给 Sequelize 方法时.

const Op = Sequelize.Op;
// 不用任何操作符别名使用 sequelize 
const connection = new Sequelize(db, user, pass, { operatorsAliases: false });
// 只用 $and => Op.and 操作符别名使用 sequelize 
const connection2 = new Sequelize(db, user, pass, { operatorsAliases: { $and: Op.and } });

如果你使用默认别名并且不限制它们,Sequelize会发出警告.如果你想继续使用所有默认别名(不包括旧版别名)而不发出警告,你可以传递以下运算符参数 -

const Op = Sequelize.Op;
const operatorsAliases = {
  $eq: Op.eq,
  $ne: Op.ne,
  $gte: Op.gte,
  $gt: Op.gt,
  $lte: Op.lte,
  $lt: Op.lt,
  $not: Op.not,
  $in: Op.in,
  $notIn: Op.notIn,
  $is: Op.is,
  $like: Op.like,
  $notLike: Op.notLike,
  $iLike: Op.iLike,
  $notILike: Op.notILike,
  $regexp: Op.regexp,
  $notRegexp: Op.notRegexp,
  $iRegexp: Op.iRegexp,
  $notIRegexp: Op.notIRegexp,
  $between: Op.between,
  $notBetween: Op.notBetween,
  $overlap: Op.overlap,
  $contains: Op.contains,
  $contained: Op.contained,
  $adjacent: Op.adjacent,
  $strictLeft: Op.strictLeft,
  $strictRight: Op.strictRight,
  $noExtendRight: Op.noExtendRight,
  $noExtendLeft: Op.noExtendLeft,
  $and: Op.and,
  $or: Op.or,
  $any: Op.any,
  $all: Op.all,
  $values: Op.values,
  $col: Op.col
};
const connection = new Sequelize(db, user, pass, { operatorsAliases });

JSON

JSON 数据类型仅由 PostgreSQL,SQLite, MySQL 和 MariaDB 语言支持.

PostgreSQL

PostgreSQL 中的 JSON 数据类型将值存储为纯文本,而不是二进制表示. 如果你只是想存储和检索 JSON 格式数据,那么使用 JSON 将占用更少的磁盘空间,并且从其输入数据中构建时间更少. 但是,如果你想对 JSON 值执行任何操作,则应该使用下面描述的 JSONB 数据类型.

MSSQL

MSSQL 没有 JSON 数据类型,但是它确实提供了对于自 SQL Server 2016 以来通过某些函数存储为字符串的 JSON 的支持.使用这些函数,你将能够查询存储在字符串中的 JSON,但是任何返回的值将需要分别进行解析.

// ISJSON - 测试一个字符串是否包含有效的 JSON
User.findAll({
  where: sequelize.where(sequelize.fn('ISJSON', sequelize.col('userDetails')), 1)
})
// JSON_VALUE - 从 JSON 字符串提取标量值
User.findAll({
  attributes: [[ sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), 'address line 1']]
})
// JSON_VALUE - 从 JSON 字符串中查询标量值
User.findAll({
  where: sequelize.where(sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), '14, Foo Street')
})
// JSON_QUERY - 提取一个对象或数组
User.findAll({
  attributes: [[ sequelize.fn('JSON_QUERY', sequelize.col('userDetails'), '$.address'), 'full address']]
})

JSONB

JSONB 可以以三种不同的方式进行查询.

嵌套对象

{
  meta: {
    video: {
      url: {
        [Op.ne]: null
      }
    }
  }
}

嵌套键

{
  "meta.audio.length": {
    [Op.gt]: 20
  }
}

外包裹

{
  "meta": {
    [Op.contains]: {
      site: {
        url: 'http://google.com'
      }
    }
  }
}

关系 / 关联

// 找到所有具有至少一个 task 的  project,其中 task.state === project.state
Project.findAll({
    include: [{
        model: Task,
        where: { state: Sequelize.col('project.state') }
    }]
})