CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
Guilherme Caraciolo
Guilherme Caraciolo

Reputation: 132

How to use row value in where clause with sequelize API

I want to achieve this sql query using sequelize.

select id, date, comment
from posts
where (date, id) > ('2018-01-01', 1)
order by date, id
limit 10

This kind of query is used for infinite scroll pagination. Any idea on how to do this? The docs doesn't say anything about row value

Upvotes: 0

Views: 540

Answers (2)

Guilherme Caraciolo
Guilherme Caraciolo

Reputation: 132

As @GondonLinoff pointed out the query can be rewrite. I had analyzed the performance of the two queries on postgres using explain analyze and it doesn't have any impact (seems that row value is only syntax sugar, but an dba expert could explain this).

So, in sequelize the where clause should be implemented as:

const filter = {}
filter[Op.or] = [
  {
    date: {
      [Op.gt]: sinceDate
    }
  },
  {
    [Op.and]: {
      date: sinceDate,
      id: {
        [Op.gt]: sinceId
      }
  }
}
const result = await Posts.findAll({ where: filter })

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271031

If the problem is the where, you can rewrite it as:

where date > '2018-01-01' or (date = '2018-01-01' and id > 1)

Upvotes: 1

Related Questions