序列化-使用以下位置自定义获取所有数据 [英] Sequelize - Get all data with custom where
问题描述
如何通过自定义条件获取数据?在此问题中， Sequelize-where子句中列的功能，我有一个类似的问题，但是我相信这个问题使用MySQL内置函数，并且它会提取半径范围内的数据.
How can I get data with custom where conditions? In this question Sequelize - function on column in a where clause, I have a similar problem, but this one I believe uses MySQL built-in functions and it fetches data within a radius.
我有几种型号.
- 房屋
- 地址
- 任务
每个 house 已经很多 任务.和每个 house 有一个 地址.
Each house HAS MANY tasks. and each house HAS ONE address.
在调用/getTasks 时，我需要获取所有任务BUT并具有以下约束:
When calling /getTasks, I need to get all the mission BUT with a constraint of:
房屋地址的直线距离必须与请求的经纬度相距N公里.
house address straight distance must be N-Kilometers away from request's lat-long.
我可以轻松地使用 findAndCountAll 进行此操作，然后在将结果返回给客户端之前进行计算，但是我确信这将运行速度较慢/效率较低，否则将破坏分页
I can just do this easily using findAndCountAll and then do the computation before returning the result to the client, BUT I'm sure this will either run slower/less efficient OR this will break the pagination.
这是我到目前为止所拥有的:
Here's what I have so far:
// 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."
})
})
}
推荐答案
我在这里发布我的解决方案.为了提供更多背景信息，此rest应用程序就像每个房屋的女佣/助手的查找器.这个特定的端点(问题)是让女佣/助手客户端应用程序获取需要清洁的可用房屋列表.条件是:
I'm posting here my solution. To give more context, this rest app is like a finder for maids/helpers for each house. And this specific endpoint (the problem) is for the maids/helpers client app to get the list of available houses that are in need of cleaning. The conditions are:
- 房子必须与客户端应用的位置相距10KM.
- 任务没有分配清洁剂.
因此，在此解决方案中，我再次摆脱了分页-这有点复杂.我认为，您不需要针对这种特定类型的应用程序设计(报价，职位列表等等)进行分页.然后从字面上计算直线距离.无需Google API.无需地理空间查询.我在这里可能是错的，但这就是我想要的-一个< = 10KM直线距离条件.
So in this solution, again, I get rid of the pagination - which kinda complicated things. In my opinion, you don't need a pagination for this specific kind of app design (listing of offers, jobs, and whatnot). And then just literally compute the straight distance. No need for Google APIs. No need for Geospatial queries. I might be wrong here, but that's all I wanted - a <= 10KM straight distance condition.
// 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."
})
})
}
这篇关于序列化-使用以下位置自定义获取所有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
