Sequelize query Or-ing where and include statements

4
votes

I am trying make a Sequelize query that returns only records that match a where clause or include wheres. For example I have a user model that belongs to a person model. The user model has one field called username and the person model has a first and last name.

Example Query:

{
    "where": {
      "username": {
        "$iLike": "%s%"
      }
    },
    "include": [{
        "model": Person,
        "where": {
            "$or": [{
                "firstName": {
                    "$iLike": "%s%"
                }
            }, {
                "lastName": {
                    "$iLike": "%s%"
                }
            }]
        }
    }]
}

The query above matches records that have a username AND (firstname or lastname) matching "ilike" 's'. I am trying to achieve username OR (firstname or lastname).

I know how do use Or operators when working inside of a where but I want to use an Or on where or include. Is this possible? Do I use required false?

1
javascriptsqlsequelize.js

1 Answers

10
votes

try this:

{
where: {
  $or: [
    {
      userName: {
        $ilike: "%s%"
      }
    },
    {
      '$person.firstName$': {
        $ilike: "%s%"
      }
    },
    {
      '$person.lastName$': {
        $ilike: "%s%"
      }
    }
  ]
},
include: [
  {
    model: Person,
  }
]
}