Sequelize - 使用 MySQL 选择所有超过 5 分钟的记录的抽象查询

Question

我正在努力使用 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';任何意见是极大的赞赏。

Answer 1

您可以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是我们想要得到的。