CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Facundo Chambo
Facundo Chambo

Reputation: 3398

Sequelize - case-insensitive like

How can I achieve this in Sequelize?

SELECT * FROM table where lower(column) LIKE ('abcd%');

I can't find a way to mix lower function with $like

Upvotes: 28

Views: 43023

Answers (4)

anniex
anniex

Reputation: 356

I run into similar problem and solved by

const values = ['adcd'].map(x => x.toLowerCase());

const results = await SomeModel.findAll({
   attributes: [
      ...Object.keys(SomeModel.rawAttributes),
      [Sequelize.fn('LOWER', Sequelize.col('someColumn')), 'lower'],
   ],
   having: { lower: values, },
});

Upvotes: 0

Pankaj Shinde
Pankaj Shinde

Reputation: 3689

You should use Sequelize.Op :

Table.findAll({
    where: {
        name: {
            [Sequelize.Op.iLike]: searchQuery
        }
    }
})

Don't forget to add % before or after your searchQuery, if you want to make a partial query.

See the docs here

Upvotes: 38

Facundo Chambo
Facundo Chambo

Reputation: 3398

I found the solution:

Table.findAll({
  attributes: ['createdAt', 'col'],
  where: {
    $and:[
      {
        createdAt:{
          $between:[minDate, maxDate]
        }
      },
      Sequelize.where(
        Sequelize.fn('lower', Sequelize.col('col')),
        {
          $like: 'abcd%'
        }
      )
    ]
  }
});

Upvotes: 27

Will Lovett
Will Lovett

Reputation: 1251

If you're using PostGres, you can use the $iLike operator to search rows (NOT column names like your question asks).

Sequelize Docs

While it doesn't fully address your question, hopefully it will help someone down the road who searches for case-insensitive + Sequelize, which brought me here.

Table.findAll({
    where: {
        createdAt: {
            $between: [minDate, maxDate]
        },
        someOtherColumn: {
            $like: '%mysearchterm%'
        }
    }
})

Upvotes: 11

Related Questions