sequelize.where function not working in model scope

[cakenggt]

I am trying to use a sequelize.where function in one of the scopes in one of my models. However, when I then do a query with that scope, the query gets mangled.

What you are doing?

Run node test.js to get the result.

models.js

module.exports = function(sequelize, DataTypes) {
  const Store = sequelize.define('store', {
    name: {
      type: DataTypes.STRING,
      validate: {
        notEmpty: true
      }
    },
    location: DataTypes.GEOGRAPHY,
  }, {
    scopes: {
      distance: function(location, radius){
        return {
          where: sequelize.where(
            sequelize.fn(
              'ST_Distance',
              sequelize.col('store.location'),
              sequelize.fn(
                'ST_GEOGFROMTEXT',
                'POINT(' + location.longitude + ' ' + location.latitude + ')'
              )
            ),
            '<=',
            radius
          )
        };
      }
    }
  });

  return {
    Store: Store
  };
};

test.js

const Sequelize = require('sequelize');
let credentials = require('./credentials');

const db = new Sequelize(credentials.TEST_DATABASE_URL, {
  logging: true
});
const models = db.import(__dirname + '/models');
let location = {latitude: 51.04, longitude: 36.09};
let radius = 5;
models.Store.scope([{
  method: [
    'distance',
    location,
    radius
  ]
}]).findAll()
.then(function(result){
  console.log(result);//just logging the result
});

What do you expect to happen?

The query to execute without errors and pull all stores that are less than or equal to 5 meters from the provided latitude and longitude. Since this code sample doesn't place any stores in the db, I just expect this code sample to execute without error.

What is actually happening?

The query executes with the error SequelizeDatabaseError: column store.attribute does not exist

Output: The query that runs follows: SELECT "id", "name", "location", "createdAt", "updatedAt", "userId" FROM "stores" AS "store" WHERE "store"."attribute" = ST_Distance("store"."location", ST_GEOGFROMTEXT('POINT(36.09 51.04)')) AND "store"."comparator" = '<=' AND "store"."logic" = 5;

Dialect: postgres
Sequelize version: 3.23.6

[sj82516]

I faced the same issue and need helps!

[celsomarques]

+1

[felixfbecker]

Please don't +1 issues. It clutters the thread without adding value to the discussion and spams maintainers with notifications. Use GitHub reactions to upvote features.

[celsomarques]

@felixfbecker Sorry about +1

@cakenggt @sj82516 I wrapped sequelize.where with $and and the error disappeared.

const lat       = query.location.coordinates[0]
    , lng       = query.location.coordinates[1];

const point = sequelize.fn('ST_MakePoint', lat, lng);
const distance = {
   $and: [
      sequelize.where(
        sequelize.fn('ST_Distance_Sphere',
        point,
        sequelize.col('location')),
        { $lte: query.distance * 1000 }
      )
   ]
};

[iamjochem]

I have also run into this bug, I can confirm that:

• returning an instance of Utils.Where as the [direct] value of the where property of the object that returned from a scope function causes sequelize to generate a broken SQL statement (i.e. containing something like: "ModelName"."comparator" = '<=' AND "Model"."logic" )
• nesting the instance of Utils.Where inside an $and array property and returning that as the where property of the object returned from the scope function makes the aforemention error go away (i.e. sequelize manages to generate the desired SQL statement)
• forgoing the use of a scope function and using the [identical] instance of Utils.Where as the where property of the options object passed to, for instance, findAll() works as expected.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[jakelowen]

I am encountering this bug as well..

scopes: {
      wildcardSearch: searchPhrase => ({
        where: Sequelize.where(
          Sequelize.fn("CONCAT",
            Sequelize.col("firstName"), 
            ' ',
            Sequelize.col("lastName"), 
            ' ',
            Sequelize.col("phone")
          ),{
            ilike: `%${searchPhrase}%`
          }
        )
      }),
...}

is producing invalid query ..... AND ("contacts"."attribute" = CONCAT("firstName", ' ', "lastName", ' ', "phone") AND "contacts"."comparator" = '=' AND "contacts"."logic" ILIKE '%393%' while passing the same {where: Sequelize.where(...)} directly to .findAll() works as expected.

[iamjochem]

this is still an issue, it's well documented and numerous people have confirmed it - the only reason there is no activity on this issue is because no maintainer has had/taken the time to do something with it. please re-open this bug.

[dangerdespain]

I'm experiencing this issue as well.

[Vincz]

Same here ...

[valeryq]

Same

[bit-crafter]

Same

[sushantdhiman]

Fixed #9730