I have a Restaurant model with a property 'location' of the type GEOMETRY("Point"). I'm trying to write a query with sequelize that will give me all the restaurants in a certain radius:
models.Restaurant.findAll({
attributes: ["*", [models.sequelize.fn("ST_Distance_Sphere", models.sequelize.fn("ST_MakePoint", latitude, longitude), models.sequelize.col("location")), "distance"]],
where: {
xxx: {
$lte: radius
}
}
}).then(function(dishes) {
res.status(200).json({dishes: dishes})
});
I have no clue what I should write where xxx is. I have tried all kinds of things but get an error every time.
In the select query I'm first calling a function to create a point from the coordinates (latitude, longitude) where to user currently is. Then I use a function to calculate the distance to the location of the restaurant.
I'm using postgres btw.
Edit: Ended up doing it like this:
models.Restaurant.findAll({
attributes: [
'id',
'name',
'addressLine1',
'addressLine2',
'city',
'zipPostalCode',
'location',
'phoneNumber',
'website',
[
models.sequelize.fn(
'ST_Distance_Sphere', models.sequelize.fn('ST_MakePoint', lat, long), models.sequelize.col('location')
),
'distance',
],
],
where: models.sequelize.and(
models.sequelize.where(
models.sequelize.fn(
'ST_Distance_Sphere', models.sequelize.fn('ST_MakePoint', lat, long), models.sequelize.col('location')
), '>', fromRadius),
models.sequelize.where(
models.sequelize.fn(
'ST_Distance_Sphere', models.sequelize.fn('ST_MakePoint', lat, long), models.sequelize.col('location')
), '<=', toRadius),
models.sequelize.where(
models.sequelize.col('Restaurant.is_online'), true
)
),
})
I'm assuming radius is that calculated point where the user is, xxx should be distance
Check the doc, notice the last query:
Model.findAll({
where: sequelize.where(sequelize.fn('FUNCTION',
sequelize.col('field')), 'value')
});
if you have to add a MySQL function to some where clause column along with other columns with their conditions, you can do it in this way.
Model.findAll({
where: {
someColumn: {[Sequelize.Op.eq]: "some value"},
columnWithFunction: Sequelize.where(Sequelize.fn("UPPER", Sequelize.col("columnWithFunction")), "=", "SOME UPPER CASE VALUE")
}
,...
})
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With