node.js - 如何在PostgreSQL的where子句中使用属性作为列名获取 "distance"不是列

Question

我已经尝试过这个答案中给出的建议，但得到了同样的错误 https://stackoverflow.com/a/66134536/12761193
当我只使用 order 时它完美地工作
下面是原始 sql 查询

SELECT "id", "firstName", "lastName", "photo", 6371 * acos(cos(radians(21.204578)) * cos(radians(latitude)) * cos(radians(72.838911) - radians(longitude)) + sin(radians(21.204578)) * sin(radians(latitude))) AS "distance" FROM "Users" AS "User" WHERE ("User"."id" != 1 AND "User"."distance" <= 29.7) ORDER BY "distance" ASC LIMIT 10 OFFSET 0;

db.User.findAll({
        attributes: ["id", "firstName", "lastName", "photo", [db.sequelize.literal("6371 * acos(cos(radians(" + latitude + ")) * cos(radians(latitude)) * cos(radians(" + longitude + ") - radians(longitude)) + sin(radians(" + latitude + ")) * sin(radians(latitude)))"), 'distance']],
        order: [[db.sequelize.literal(`"distance"`), 'ASC']],
        where: db.sequelize.where(db.sequelize.literal(`"distance" <= ${km} AND "id" != ${1}`)),
        limit,
        offset
    })
        .then(function (data) {
            console.log(data);
            res.json({
                response_code: 'SUCCESS',
                data: data
            });
        }).catch(err => {
            console.log(err);
            res.status(500).end();
        })

Answer 1

我能够使用以下文字解决这个问题

    db.User.findAndCountAll({
        attributes: ["id", "firstName", "lastName", "photo", [db.sequelize.literal("6371 * acos(cos(radians(" + latitude + ")) * cos(radians(latitude)) * cos(radians(" + longitude + ") - radians(longitude)) + sin(radians(" + latitude + ")) * sin(radians(latitude)))"), 'distance']],
        order: [[db.sequelize.literal(`"distance"`), 'ASC']],
        where: db.sequelize.literal(`6371 * acos(cos(radians(${latitude})) * cos(radians(latitude)) * cos(radians(${longitude}) - radians(longitude)) + sin(radians(${latitude})) * sin(radians(latitude))) <= ${km} AND "id" != ${req.userDetails.UserId}`),
        limit,
        offset
    })