Sequelize: Concat fields in WHERE LIKE clause

Question

I am using the sequelize ORM for a node.js project I am working on. One query I have, I need to perform a like operation on the concatenated result of multiple columns.

For instance, something like the following:

SELECT * FROM People WHERE (CONCAT(firstname, ' ', lastname)) LIKE '%John Do%'.

I am using the following syntax and would like to know if this is possible without having to resort to using RAW queries (which is nowhere else in my solution).

   var criteria = {
        include: [
            occupation
        ],
        where: {
            is_active: 1
        },
        nest: false
    };

    db.people.findAll(criteria, {}).then(function(people) {
        success(people);
    }).catch(function(err) {
        error(err);
    });

Any ideas?

Answer 1

You'll need something like this

var criteria = {
    where: Sequelize.where(Sequelize.fn("concat", Sequelize.col("firstname"), Sequelize.col("lastname")), {
        like: '%John Do%'
    })
}

Note: untested

Original source

Answer 2

I was able to achieve this with the new sequelize version 5.21.13 based on @yjimk answer.

Users.findAll({
  where: {
    [sequelize.Op.or]:{
     namesQuery: sequelize.where(
      sequelize.fn(
        "concat",
        sequelize.col("firstName"),
        " ",
        sequelize.col("lastName")
      ),
      {
        [sequelize.Op.like]: `%${req.body.query}%`,
      }
    ),
    email: {[sequelize.Op.like]: `%${req.body.query}%`},
    companyName: {[sequelize.Op.like]: `%${req.body.query}%`},
  }
})

Answer 3

Inspired by @code-jaff but you need to concatenate a space string in between first and last names to make this work correctly. Otherwise it would only return for 'JohnDoe' and not for 'John Doe'. Here's the code.

  Sequelize.where(Sequelize.fn('concat', Sequelize.col('firstName'), ' ', Sequelize.col('lastName')), {
    like: '% John Doe %'
  })

To provide some context for people who might not understand where this would fit into your query, this is an example of the above code in a where or statement. req.body.query being the variable search term that you're POSTing.

Users.findAll({
  where: {
    $or: [
      Sequelize.where(Sequelize.fn('concat', Sequelize.col('firstName'), ' ', Sequelize.col('lastName')), {
        like: '%' + req.body.query + '%'
      }),
        { email: { $like: '%' + req.body.query + '%' } },
        { companyName: { $like: '%' + req.body.query + '%' } }
    ]
  }
})

Update for Sequelize 4.0

String based operators ($like and $or in the above example) have been deprecated in favour of symbol based operators. It's a good thing for security

See: http://docs.sequelizejs.com/manual/tutorial/querying.html#operators

These operators would be replaced with [Sequelize.Op.like] and [Sequelize.Op.or]. There are also other ways to configure it in your sequelize options highlighted in their documentation