OceansOnPluto OceansOnPluto - 4 months ago 23
SQL Question

Query two combined fields at once in Sequelize

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?

Answer

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