node.js - Sequelize - 使用自定义 where 获取所有数据
问题描述
如何使用自定义 where 条件获取数据?在这个问题Sequelize - function on column in a where clause中,我遇到了类似的问题,但我相信这个问题使用 MySQL 内置函数,它会在半径范围内获取数据。
我有几个模型。
- 房子
- 地址
- 任务
每个都有house
很多 tasks
。每个都有house
一个 address
。
调用时/getTasks
,我需要获得所有任务但具有以下约束:
房屋地址直线距离必须距离请求的经纬度 N 公里。
我可以很容易地做到这一点findAndCountAll
,然后在将结果返回给客户端之前进行计算,但我确信这会运行得更慢/效率更低,或者这会破坏分页。
这是我到目前为止所拥有的:
// Get all the available tasks.
// Requirements:
// 1. It accepts the coordinate from the client.
// 2. The client's coordinate must be <= N-Kilometer straight distance.
// 3. Return the tasks WITH PAYMENT and WITHOUT assigned USER.
exports.getTasks = (req, res) => {
const latitude = parseFloat(req.query.latitude)
const longitude = parseFloat(req.query.longitude)
if (!longitude || !latitude) {
return res.status(200).send({
errorCode: 101,
message: "Error! Required parameters are: {longitude} and {latitude}."
})
}
const page = myUtil.parser.tryParseInt(req.query.page, 0)
const limit = myUtil.parser.tryParseInt(req.query.limit, 10)
const houseLat = 32.9697
const houseLong = -96.80322
console.log("Computing distance of a house (" + latitude + ", " + longitude + ") --- to (" + houseLat + ", " + houseLong + ")")
point1 = new GeoPoint(latitude, longitude)
point2 = new GeoPoint(pLat, pLong)
const distance = point1.distanceTo(point2, true)
// Begin query...
db.Task.findAndCountAll({
where: null, // <----- don't know what to put.
include: [
{
model: db.Order,
as: "order"
},
{
model: db.House,
as: "house",
include: [
{
model: db.Address,
as: "address"
}
]
}
],
offset: limit * page,
limit: limit,
order: [["id", "ASC"]],
})
.then(data => {
res.json(myUtil.response.paging(data, page, limit))
})
.catch(err => {
console.log("Error get all tasks: " + err.message)
res.status(500).send({
message: "An error has occured while retrieving data."
})
})
}
解决方案
我在这里发布我的解决方案。为了提供更多背景信息,这个休息应用程序就像一个寻找每个房子的女佣/帮手的工具。这个特定的端点(问题)是为女佣/助手客户端应用程序获取需要清洁的可用房屋列表。条件是:
- 房屋必须在距离客户端应用程序位置 10 公里的直线距离内。
- 该任务没有指定的清洁工。
所以在这个解决方案中,我再次摆脱了分页——这有点复杂。在我看来,对于这种特定类型的应用程序设计(报价、工作和诸如此类的列表),您不需要分页。然后从字面上计算直线距离。无需 Google API。无需地理空间查询。我在这里可能错了,但这就是我想要的 - <= 10KM 直线距离条件。
// Get all the available tasks.
// We are not using pagination here...
// Makes our lives easier.
// Requirements:
// 1. It accepts the coordinate from the client.
// 2. The client's coordinate must be <= N-Kilometer straight distance.
// 3. Return the tasks WITH ORDER and WITHOUT USER.
exports.getAvailableMissions = (req, res) => {
const latitude = parseFloat(req.query.latitude)
const longitude = parseFloat(req.query.longitude)
if (!longitude || !latitude) {
return res.status(200).send({
errorCode: 101,
message: "Error! Required parameters are: {longitude} and {latitude}."
})
}
// Proceed with searching...
// user id must equal to null. - means unassigned.
// order must not be equal to null. - means paid.
// the order condition is in the promise.
const condition = { userId: { [op.is]: null } }
// Begin query...
db.Mission.findAndCountAll({
where: condition,
include: [
{
model: db.Order,
as: "order"
},
{
model: db.Asset,
as: "asset"
},
{
model: db.House,
as: "house",
include: [
{
model: db.Address,
as: "address"
}
]
}
],
limit: 10,
order: [["id", "ASC"]],
})
.then(data => {
let newData = JSON.parse(JSON.stringify(data))
const tasks = newData.rows
let newRows = []
for (let task of tasks) {
const house = task.house
const address = house.address
const houseLat = address.latitude
const houseLong = address.longitude
const point1 = new GeoPoint(latitude, longitude)
const point2 = new GeoPoint(houseLat, houseLong)
const distance = point1.distanceTo(point2, true)
const distanceInMiles = distance * 0.621371
console.log("Computing distance (" + latitude + ", " + longitude + ") --- to (" + houseLat + ", " + houseLong + ")")
console.log("Miles: distance: ", distanceInMiles)
// 10 miles max straight distance.
const maxDistance = 10
if (distanceInMiles <= maxDistance) {
task.distanceFromMeInMiles = parseFloat(distanceInMiles)
newRows.push(task)
}
}
// Apply the new rows.
delete newData.rows
delete newData.count
newData.total = newRows.length
newData.data = newRows
res.json(newData)
})
.catch(err => {
console.log("Error get all tasks: " + err.message)
res.status(500).send({
message: "An error has occured while retrieving data."
})
})
}
推荐阅读
- java - 包 javax 不可访问
- javascript - 为什么在 JavaScript 中添加值会扩展数组长度?
- reactjs - React Native Paper 按钮不会触发 onpress
- sql - 对 sqlite3 中具有相同 id 的所有行求和
- bootstrap-4 - bootstrap float-right 不向右移动元素
- python - 在 python 中寻找路径?
- bash - AWS CodeDeploy [stderr]/usr/bin/env: node: No such file or directory
- linux - 在 docker-compose 中运行 bash 命令
- google-apps-script - 可安装触发器以不同形式触发脚本?
- regex - CloudWatch 洞察日志解析