如何在带有 sequelize API 的 where 子句中使用行值 [英] How to use row value in where clause with sequelize API

问题描述

我想使用 sequelize 来实现这个 sql 查询.

选择id、日期、评论来自帖子其中(日期，ID)>('2018-01-01', 1)按日期排序，id限制 10

这种查询用于无限滚动分页.关于如何做到这一点的任何想法?文档没有说明行值

解决方案

正如@GondonLinoff 指出的，查询可以重写.我已经使用 explain analyze 分析了 postgres 上两个查询的性能，它没有任何影响(似乎行值只是语法糖，但 dba 专家可以解释这一点).>

因此，在 sequelize 中，where 子句应该实现为:

const 过滤器 = {}过滤器[Op.or] = [{日期: {[Op.gt]:自日期}},{[操作和]:{日期:自日期，ID: {[Op.gt]:自从Id}}}const result = await Posts.findAll({ where: filter })

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

解决方案

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 })

这篇关于如何在带有 sequelize API 的 where 子句中使用行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！