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 · Accepted Answer · 2017-07-07 11:49:17Z

2

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.

edited Jul 7, 2017 at 11:49

answered Jul 7, 2017 at 11:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ThomasThiebaud Over a year ago

I need to use this.database.col('records', 'id') in my sequelize to make it work. I will accept the answer as soon as I can.

Łukasz Smoczyński · Accepted Answer · 2019-07-05 12:09:50Z

0

Easiest way to do it in sequelize would be by passing 'records.id' to this.database.col(). By that Sequelize shouldn't quote identifier.

Actually you should use this function with each and every column you pass to Sequelize. By doing that Sequelize can generate more accurate queries.

answered Jul 5, 2019 at 12:09

Łukasz Smoczyński

335 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related