Sequelize: Concat fields in WHERE LIKE clause

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

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