Using dynamic search parameters with Sequelize.js

Question

I'm trying to follow the Sequelize tutorial on their website.

I have reached the following line of code.

Project.findAll({where: ["id > ?", 25]}).success(function(projects) {
  // projects will be an array of Projects having a greater id than 25
})

If I tweak it slightly as follows

Project.findAll({where: ["title like '%awe%'"]}).success(function(projects) {
    for (var i=0; i<projects.length; i++) {
        console.log(projects[i].title + " " + projects[i].description);
    }
});

everything works fine. However when I try to make the search parameter dynamic as follows

Project.findAll({where: ["title like '%?%'", 'awe']}).success(function(projects) {
    for (var i=0; i<projects.length; i++) {
        console.log(projects[i].title + " " + projects[i].description);
    }
});

It no longer returns any results. How can I fix this?

I found the wording of your question a little confusing, so I edited it. I hope I understood correctly, please feel free to change it back if you think I screwed up. — user1114
– user1114, Commented Feb 17, 2012 at 0:32

Javier Cornejo Alfaro · Accepted Answer · 2017-07-12 18:05:51Z

26

Now on Sequelize you can try this

{ where: { columnName: { $like: '%awe%' } } }

See http://docs.sequelizejs.com/en/latest/docs/querying/#operators for updated syntax

edited Jul 12, 2017 at 18:05

answered Aug 26, 2014 at 22:21

Javier Cornejo Alfaro

5115 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Val Over a year ago

Good simplistic answer. Quick note for new users, title is the name of the column you are wanting to find the match against.

Javier Cornejo Alfaro Over a year ago

Thanks @JMM I fixed it ;)

sertsedat · Accepted Answer · 2019-08-27 15:16:31Z

18

I think you would do that like this:

where: ["title like ?", '%' + 'awe' + '%']

So if you were doing this with an actual variable you'd use:

Project.findAll({where: ["title like ?", '%' + x + '%']}).success(function(projects) {
    for (var i=0; i<projects.length; i++) {
        console.log(projects[i].title + " " + projects[i].description);
    }
});

edited Aug 27, 2019 at 15:16

sertsedat

3,6101 gold badge28 silver badges45 bronze badges

answered Feb 17, 2012 at 0:35

Paul

142k28 gold badges285 silver badges272 bronze badges

Comments

Nick Chapsas · Accepted Answer · 2019-07-12 13:28:55Z

6

Please try this code

const Sequelize = require('sequelize');
const Op = Sequelize.Op;
{ where: { columnName: { [Op.like]: '%awe%' } } }

edited Jul 12, 2019 at 13:28

Nick Chapsas

7,2501 gold badge22 silver badges30 bronze badges

answered Jun 26, 2018 at 6:19

Manoj Rana

3,4982 gold badges27 silver badges36 bronze badges

Comments

mPrinC · Accepted Answer · 2013-12-07 08:49:20Z

4

I would do it in this way:

Project.findAll({where: {title: {like: '%' + x + '%'}, id: {gt: 10}}).success(function(projects) {
  for (var i=0; i<projects.length; i++) {
    console.log(projects[i].title + " " + projects[i].description);
  }
});

In this way you can have nicely more WHERE clausas

answered Dec 7, 2013 at 8:49

mPrinC

9,4332 gold badges37 silver badges34 bronze badges

Comments

futbolpal · Accepted Answer · 2013-09-16 22:24:51Z

0

It might be cleaner to leverage the Sequelize.Utils.format function

answered Sep 16, 2013 at 22:24

futbolpal

1,3882 gold badges13 silver badges20 bronze badges

Comments

Bradley Smock · Accepted Answer · 2018-12-21 17:25:11Z

The accepted answer of ["columnName like ?", '%' + x + '%'] for the where clause results in this error in Sequelize 4.41.1: "Support for literal replacements in the where object has been removed."

Assuming: modelName.findAll({ where : { columnName : { searchCriteria } } });

Using [Op.like]: '%awe%' or $like: '%awe%' } as the searchCriteria (where 'awe' is the value you want to find in columnName) both result in SQL with a LIKE clause of LIKE '\"%awe%\"'. Notice the extra quotation marks. [Op.like] and $like are aliases of each other and neither answer the OP's question because they don't allow dynamic search parameters.

Using [Op.like] : `%${parameter}%` as the searchCriteria (where 'parameter' is the parameter whose value you want to find in columnName) resulted in SQL with a LIKE clause of LIKE '\"%findMe\"' when parameter= 'findMe'. Again, notice the extra quotation marks. No results.

An answer in another StackOverflow post suggested using [Op.like]: [`%${parameter}%`] for the searchCriteria (where 'parameter' is the parameter whose value you want to find in columnName). Notice the square brackets! This resulted in SQL with a LIKE clause of LIKE '[\"%findMe%\"]' when parameter= 'findMe'. Again notice the extra quotation marks and the square brackets. No results.

For me, the solution was to use a raw query: Sequelize.query('SELECT * FROM tableName WHERE columnName LIKE "%searchCriteria%"');

Collectives™ on Stack Overflow

Using dynamic search parameters with Sequelize.js

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related