Why does postgres (with sequelize) count 1 if there is no value?

Question

I'm using sequelize to count the number of messages that a device received during the last 24 hours.

Sequelize is going to execute the following request (I attached sequelize code at the end)

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";

which return

id   count
24  25790
163 1
95  1

the value for the id 24 is correct, but the other values should be 0.

I then manually execute this SQL query

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;

which gives me the expected result.

I'm not able to see the difference between the two queries. Does someone know why the second is working?

The sequelize request is the following

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'],
})

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-07-07T11:48:07

The SQL has single quotes around records.id, so SQL is counting a string value (which is never NULL). You want:

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

I don't know how to express this in sequelize, but the problem is the single quotes.

Why does postgres (with sequelize) count 1 if there is no value?

2 Answers