将LIKE语句中的多个值序列化

Question

我正试图在Sequelize:中复制此查询

SELECT * FROM Employee WHERE name LIKE 'john%' or name LIKE 'jane%'

目前，我有这个：

where: {
name: Sequelize.where(
Sequelize.fn('LOWER', Sequelize.col(employee.name)),
'LIKE',
'john%'
)
}

但是如何在Sequelize中处理多个LIKE语句呢？我试着把Sequelize.where的第三个参数变成这样的数组：

where: {
name: Sequelize.where(
Sequelize.fn('LOWER', Sequelize.col(employee.name)),
'LIKE',
['john%', 'jane%']
)
}

但我得到了这个错误：SequelizeDatabaseError: Invalid usage of the option NEXT in the FETCH statement

Answer 1

下面是一个使用"sequelize": "^5.21.3":的示例

import { sequelize } from '../../db';
import Sequelize, { Model, DataTypes, Op } from 'sequelize';
class Employee extends Model {}
Employee.init(
{
name: DataTypes.STRING,
},
{ sequelize, modelName: 'Employee' },
);
(async function test() {
try {
await sequelize.sync({ force: true });
// seed
await Employee.bulkCreate([{ name: 'james' }, { name: 'JOHN' }, { name: 'JANE' }]);
// test
const result = await Employee.findAll({
where: {
name: {
[Op.or]: [
Sequelize.where(Sequelize.fn('LOWER', Sequelize.col('name')), 'LIKE', 'john%'),
Sequelize.where(Sequelize.fn('LOWER', Sequelize.col('name')), 'LIKE', 'jane%'),
],
},
},
raw: true,
});
console.log(result);
} catch (error) {
console.log(error);
} finally {
await sequelize.close();
}
})();

执行结果：

Executing (default): DROP TABLE IF EXISTS "Employee" CASCADE;
Executing (default): DROP TABLE IF EXISTS "Employee" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "Employee" ("id"   SERIAL , "name" VARCHAR(255), PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'Employee' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "Employee" ("id","name") VALUES (DEFAULT,'james'),(DEFAULT,'JOHN'),(DEFAULT,'JANE') RETURNING *;
Executing (default): SELECT "id", "name" FROM "Employee" AS "Employee" WHERE (LOWER("name") LIKE 'john%' OR LOWER("name") LIKE 'jane%');
[ { id: 2, name: 'JOHN' }, { id: 3, name: 'JANE' } ]

数据库中的数据记录：

=# select * from "Employee";
id | name
----+-------
1 | james
2 | JOHN
3 | JANE
(3 rows)

Answer 2

回复对你来说已经很晚了，但这可能会帮助其他人解决你的问题和我的答案。：(您可以简单地传递一个对象数组，如：

let employeesName = [{ name: 'james' }, { name: 'JOHN' }, { name: 'JANE' }]);
await Employee.findAll({
where: {
[Op.or]: employeesName,
},
})
.then((result) => {
console.log(result);
})
.catch((error) => {
console.log(error);
})