Query two combined fields at once in Sequelize

2
votes

I have a search box for people's names in my application. Candidate's names are stored as firstName and then lastName. When I search the application, the application input submits a call to an ajax function, where I have this piece of code. 

  filters.where = {
    $or: ['firstName', 'lastName', 'email'].map((item) =>
      ({[item]: {[queryClause]: `%${query}%`}}))
  };

  const scope = req.query.list;
  const candidates = await CandidateModel.scope(scope).findAll(filters);

Hence if I type in the search box "John", it will find the candidate, and if I type in the word "Smith" it will find the candidate.

The problem is, if I type in the full name "John Smith" it won't come up, because the query is checking to see if "John Smith" equals "John", i.e. the first name, or if "John Smith" equals "Smith", the last name. It doesn't equal either of these.

Is there a way to filter via combined fields in sequalize, so it tests if the query matches the firstName AND lastName fields combined?

3
sqlnode.jssequelize.js

3 Answers

14
votes

use something like the following to search by full name in this form "firstName lastName"

Sequelize.where(Sequelize.fn("concat", Sequelize.col("firstName"), ' ', Sequelize.col("lastName")), {
                        $ilike: '%john smith%'
                    })

by doing so you fix the issue of firstname or last name might have spaces.

2
votes

Seems like you might have to split the query input and search all fields on the terms passed in. for example:

var queryClause ='John Smith';
filters.where = {
    $or: _.flatten(_.map(['firstName', 'lastName', 'email'], function(){
        return _.map(queryClause.split(' '), function(q){
            return {[item]: { $like : '%'+q+'%'}}
        })
    }))
 }

which would output something like:

{
    "where": {
        "$or": [{
                "firstName": {
                    "$like": "%John%"
                }
            }, {
                "firstName": {
                    "$like": "%Smith%"
                }
            }, {
                "lastName": {
                    "$like": "%John%"
                }
            }, {
                "lastName: {
                "$like": "%Smith%"
            }
        },
        {
            "email": {
                "$like": "%John%"
            }
        },
        {
            "email": {
                "$like": "%Smith%"
            }
        }]
}
}

--btw using lodash in the above example code

0
votes

I could not find how to do it natively with sequelize, but you always can use raw queries

sequelize.query('SELECT * FROM candidates where (firstName+lastName) like query OR email like query', { model: Candidate})
  .then(function(projects){
    // Each record will now be a instance of Candidate
})