node.js - 如果没有值(value)，为什么 postgres(带有 sequelize)计数为 1？

Question

我正在使用 sequelize 来计算设备在过去 24 小时内收到的消息数。

Sequelize 将执行以下请求(我在最后附上了 sequelize 代码)

SELECT "device"."id", COUNT('records.id') AS "count"
FROM "device" AS "device"
  LEFT OUTER JOIN "record" AS "records" ON "device"."id" = "records"."deviceId" AND "records"."date" > '2017-07-06 08:09:40.468 +00:00'
WHERE "device"."typeId" = '2'
GROUP BY "device"."id";

返回

id   count
24  25790
163 1
95  1

id 24 的值是正确的，但其他值应该是 0。

然后我手动执行这个 SQL 查询

SELECT device.id, COUNT(record.id) AS count
FROM device AS device
  LEFT OUTER JOIN record ON device.id = record."deviceId" AND record.date > '2017-07-06 08:09:40.468 +00:00'
WHERE device."typeId" = '2'
GROUP BY device.id;

这给了我预期的结果。

我看不出这两个查询之间的区别。有人知道为什么第二个有效吗？

---

sequelize请求如下

const devices = await this.database.model('device').findAll({
  where: {
    typeId: req.params.id,
  },
  attributes: {
    include: [
      'id',
      [this.database.fn('COUNT', 'records.id'), 'count'],
    ],
  },
  include: [{
    model: this.database.model('record'),
    where: {
      date: {
        $gt: oneDayAgo,
      },
    },
    attributes: [],
    required: false,
  }],
  group: ['device.id'],
})

Answer 1

SQL 在 records.id 周围有单引号，因此 SQL 正在计算一个字符串值(永远不会是 NULL)。你想要:

SELECT "device"."id", COUNT(records.id) AS "count"

我不知道如何在 sequelize 中表达这一点，但问题是单引号。