节点Js将原始查询更改为纬度和经度的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在have和order子句中使用别名存在一些问题。因此，sequelize.literal在这种情况下我总是使用方法。CubbersShopsData.findOne({&nbsp; &nbsp; where: { cubbersId: data.cubbersId },&nbsp; &nbsp; include: [ CubbersAvailability ],&nbsp; &nbsp; attributes: [[sequelize.literal("3959 * acos(cos(radians("+shopLatitude+")) * cos(radians(shopLatitude)) * cos(radians("+shopLongitude+") - radians(shopLongitude)) + sin(radians("+shopLatitude+")) * sin(radians(shopLatitude)))"),'distance']],&nbsp; &nbsp; having: sequelize.literal('distance < 20'),&nbsp; &nbsp; order: sequelize.literal('distance ASC'),&nbsp; &nbsp; logging: console.log}).then(dataList=>{&nbsp; &nbsp; res.send(dataList)}).catch(error=>{&nbsp; &nbsp; res.send('error')});