节点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({

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

});