javascript - Node Js sequelize 原始查询更改为经纬度的 ORM

Question

我正在尝试将我的 sequelize 原始查询隐藏到 ORM，但没有得到正确的结果。

这是我的原始查询

var query = "SELECT ( 3959 * acos( cos( radians('40.892389') ) * cos( radians( c.shopLatitude ) ) * cos( radians( c.shopLongitude ) - radians('-74.258633') ) + sin( radians('40.892389') ) * sin(radians(shopLatitude)) ) ) AS distance FROM cubbers c HAVING distance < 20 ORDER BY distance ASC";

db.sequelize.query(query, { type: sequelize.QueryTypes.SELECT} ).then(dataList=>{
    res.send(dataList)
}).catch(error=>{
    res.send('error')
});

ORM:

CubbersShopsData.findOne({
    where: { cubbersId: data.cubbersId },
    include: [ CubbersAvailability ],
    attributes: [[sequelize.literal("3959 * acos(cos(radians("+shopLatitude+")) * cos(radians(shopLatitude)) * cos(radians("+shopLongitude+") - radians(shopLongitude)) + sin(radians("+shopLatitude+")) * sin(radians(shopLatitude)))"),'distance']],
    logging: console.log
}).then(dataList=>{
    res.send(dataList)
}).catch(error=>{
    res.send('error')
});

在我的 ORM 代码中，我想添加 orderBy 和 having。

如果我添加 order:['distance', 'ASC'], 它会显示此错误

Unknown column 'cubbers.distance' in 'order clause

Answer 1

不幸的是，sequelize 有一些 problems with 在 having 和 order 子句中使用别名。因此，在这种情况下，我总是使用 sequelize.literal 方法。

CubbersShopsData.findOne({
    where: { cubbersId: data.cubbersId },
    include: [ CubbersAvailability ],
    attributes: [[sequelize.literal("3959 * acos(cos(radians("+shopLatitude+")) * cos(radians(shopLatitude)) * cos(radians("+shopLongitude+") - radians(shopLongitude)) + sin(radians("+shopLatitude+")) * sin(radians(shopLatitude)))"),'distance']],
    having: sequelize.literal('distance < 20'),
    order: sequelize.literal('distance ASC'),
    logging: console.log
}).then(dataList=>{
    res.send(dataList)
}).catch(error=>{
    res.send('error')
});