Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sequelize - function on column in a where clause

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
      )
    ),
  })
like image 986
Joachim Avatar asked Jan 10 '17 21:01

Joachim


2 Answers

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')
});
like image 182
jesusbv - user3085938 Avatar answered Sep 20 '22 17:09

jesusbv - user3085938


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")
     }
,...
})
like image 35
Kashif Ali Avatar answered Sep 20 '22 17:09

Kashif Ali