Query concatenated fields in Sequelize

0
votes

I am implementing an application using Sequelize to handle communication with the database.

I have a number of users in the database and I want to implement some search-functionality which allows users to find other users based on their full name. A user has (amongst other attributes) a firstName and a lastName in its model.

A user who is looking for another user can search for "John Doe", where John is the first name and Doe is the last name. Unfortunately, the first name and last name are stored in separate fields in my model. Because of this, I need to concatenate the firstName and lastName field in the "where"-clause as I tried below.

In the where-clause I am just concatenating firstName and lastName and then check whether that is "like" the full name that is passed as the argument name. I think the intention of this code below is clear. It is however not working (error says it doesn't expect the '(' after concat so this syntax isn't allowed). Is there an easy trick to do this or should I write my own query using sequelize.query? 

var findUserByName = function(name) {
    return models.User.find({where: {concat(firstName,' ',lastName): like: name}});
}
2
mysqlnode.jsconcatsequelize.js

2 Answers

1
votes

This you can try in where clause

where: {
  $and: [
    Sequelize.where(
      Sequelize.fn('concat', Sequelize.col('firstName'), ' ', Sequelize.col('lastName')), {
        like: '%'+name+'%'
      }
    )
  ]
}
0
votes

You could do that like this:

User.find({
    where: {
        $or: [
            name: {
                $like: ['%', firstName, '%'].join('')
            },
            lastName: {
                $like: ['%', lastName, '%'].join('')
            }
        ]
    }).success(function (user) {});