javascript - 如何通过sequelize.js中的函数进行过滤

Question

我正在尝试按函数过滤结果，然后使用函数列对结果集进行排序。这是到目前为止我的代码:

db.trip.findAll({
  attributes: [
    [
      db.sequelize.fn('SUM', 
        db.sequelize.col('departure_time'),
        db.sequelize.col('arrival_time')),
      'summation'
    ]
  ],
  where: {
    summation: {
      gte: 500
    }
  },
  order: [['summation', 'ASC']],
  logging: console.log
}).then(function(results) {

});

生成的 SQL 使得重新引用在属性中创建的别名变得困难:

SELECT SUM("departure_time", "arrival_time") AS "summation"
FROM "trip" AS "trip" 
WHERE "trip"."summation" >= 500 
ORDER BY "trip"."summation" ASC;

关于如何在不使用整个查询文字的情况下完成这项工作有什么想法吗？

Answer 1

我找到了一种方法，将文字语句用作别名。我使用的是 Postgres，所以我不确定引用是否适用于其他方言:

var summationAlias = db.sequelize.literal('"summation"');

db.trip.findAll({
  attributes: [
    [
      db.sequelize.fn('SUM', 
        db.sequelize.col('departure_time'),
        db.sequelize.col('arrival_time')),
      'summation'
    ]
  ],
  where: db.sequelize.where(summationAlias, '>=', 500),
  order: [[summationAlias, 'ASC']],
  logging: console.log
}).then(function(results) {

});

上面的代码生成以下 SQL:

SELECT SUM("departure_time", "arrival_time") AS "summation"
FROM "trip" AS "trip" 
WHERE "summation" >= 500 
ORDER BY "summation" ASC;