Unaccent in Sequelize

user_3549710 0
Viewed 2856

I'm currently working in a project that uses ExpressJS, PostgreSQL and Sequelize as the ORM. I developed a search function that makes a query that searches items by name:

models.foo.findAll({
  where: {
    $or: [
      {name: {$ilike: keywords}},
      {searchMatches: {$contains: [keywords]}}
    ]
  },
  order: [['name', 'ASC']]
})

This works fine, but if the name contains an special character (like á, é, í, ó or ú) this query won't find it.

Is there a way to make the query search names with speacial characters in a meaningful sense? Like if I search the name "potato" the results "The potato", "Da potátos" and "We are the pótatóes" will come out, but not "We eat pátatos" (since á != o)

sequelize.js express node.js postgresql
4 Answers

This can now be done without a completely RAW query, but using Sequelize's in built functions:

models.foo.findAll({
  where: Sequelize.where(
    Sequelize.fn('unaccent', Sequelize.col('name')), {
        [Op.iLike]:`%${keywords}%`
  }),
  order: [['name', 'ASC']]
})

Then ordering, associations etc. all work still as normal :).

user_5745190

In my case I solved this question using the Sequelize.literal and COLLATE that way:

where: Sequelize.literal(`name COLLATE Latin1_general_CI_AI like '%${keywords}%' COLLATE Latin1_general_CI_AI`)

That way, removing the accents on both sides.

user_26570820
Related