javascript - Sequelize - 使用 MySQL 选择所有超过 5 分钟的记录的抽象查询
问题描述
我正在努力使用 sequelize 编写一个抽象查询,该查询将使用“updatedAt”列识别超过 5 分钟的记录。我的查询如下:
const Jts = await models.Jts.findOne({
where: {
publish: true,
retry: {
[Op.lte]: 3
},
updatedAt: {
[Op.lte]: [sequelize.fn("(NOW() - INTERVAL 5 MINUTE)")]
}
},
include: ["OrgSetEntries"],
paranoid: false,
order: [
["effectiveDate", "ASC"],
["updatedAt", "ASC"]
]
});
我尝试了一些语法,但正在苦苦挣扎。生成的查询总是产生“无效日期”字符串,例如:
... updatedAt` <= 'Invalid date';
任何意见是极大的赞赏。
解决方案
您可以Sequelize.literal("NOW() - (INTERVAL '5 MINUTE')")
用来获取 5 分钟前更新的数据行。
例如
import { sequelize } from '../../db';
import Sequelize, { Model, DataTypes, Op } from 'sequelize';
class Jts extends Model {}
Jts.init(
{
publish: DataTypes.BOOLEAN,
retry: DataTypes.INTEGER,
updatedAt: DataTypes.DATE,
},
{ sequelize, modelName: 'jts' },
);
(async function test() {
try {
// create tables
await sequelize.sync({ force: true });
// seed
let date1 = new Date();
date1.setHours(date1.getHours() - 1);
await Jts.bulkCreate([
{ publish: false, retry: 2 },
{ publish: false, retry: 5 },
{ publish: true, retry: 2, updatedAt: date1 },
{ publish: true, retry: 1, updatedAt: new Date() },
]);
// test
const result = await Jts.findOne({
where: {
publish: true,
retry: {
[Op.lte]: 3,
},
updatedAt: {
[Op.lte]: Sequelize.literal("NOW() - (INTERVAL '5 MINUTE')"),
},
},
raw: true,
});
console.log('result:', result);
} catch (error) {
console.log(error);
} finally {
await sequelize.close();
}
})();
执行结果:
Executing (default): DROP TABLE IF EXISTS "jts" CASCADE;
Executing (default): DROP TABLE IF EXISTS "jts" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "jts" ("id" SERIAL , "publish" BOOLEAN, "retry" INTEGER, "updatedAt" TIMESTAMP WITH TIME ZONE, 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 = 'jts' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "jts" ("id","publish","retry","updatedAt") VALUES (DEFAULT,false,2,NULL),(DEFAULT,false,5,NULL),(DEFAULT,true,2,'2020-04-16 12:50:43.458 +00:00'),(DEFAULT,true,1,'2020-04-16 13:50:43.458 +00:00') RETURNING *;
Executing (default): SELECT "id", "publish", "retry", "updatedAt" FROM "jts" AS "jts" WHERE "jts"."publish" = true AND "jts"."retry" <= 3 AND "jts"."updatedAt" <= NOW() - (INTERVAL '5 MINUTE') LIMIT 1;
result: { id: 3,
publish: true,
retry: 2,
updatedAt: 2020-04-16T12:50:43.458Z }
检查数据库:
node-sequelize-examples=# select * from "jts";
id | publish | retry | updatedAt
----+---------+-------+----------------------------
1 | f | 2 |
2 | f | 5 |
3 | t | 2 | 2020-04-16 12:50:43.458+00
4 | t | 1 | 2020-04-16 13:50:43.458+00
(4 rows)
一小时前更新的数据行id = 3
是我们想要得到的。
推荐阅读
- json - Angular6 Div 围绕来自同一用户的数组
- javascript - 单击其中的下拉菜单消失
- consul - spring cloud consul config app使用哪些端口与consul通信?
- regex - 正则表达式 - 提取字符串
- java - 如何更改继承类中的集合类型?
- tinymce - 从 tinymce 向页面添加内容
- ios - UIProgressView 没有在 Swift3 iOS 中的 UICollectionView 内更新
- android - 为什么片段类应该是公开的?
- python - 在 Python 中使用字符串调用内置函数
- python - 对某事的编码仅发生在某些编码中?