CODEWITHSUNDEEP
node.jspostgresqlexpresssequelize.js
Potray
Potray

Reputation: 1998

Unaccent in Sequelize

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)

Upvotes: 4

Views: 4223

Answers (4)

xbral
xbral

Reputation: 91

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.

Upvotes: 0

Jamie
Jamie

Reputation: 1014

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 :).

Upvotes: 7

Potray
Potray

Reputation: 1998

I finally found a valid solution. First I created the unaccent extension:

create extension unaccent;

Then I just used a raw query (I couldn't figure out how to build the query using Sequelize's way) like this:

models.sequelize.query(
    `SELECT
      *
    FROM
      "Foos"
    WHERE
      unaccent("name") ilike unaccent('${keywords}')          
      OR "searchMatches" @> ARRAY[unaccent('${keywords}')]::VARCHAR(255)[]
    ORDER BY
      "name" ASC`, {model: models.Foo})

And it works!

Upvotes: 4

Scoots
Scoots

Reputation: 3102

A dictionary might be what you are looking for. Can basically be used to map synonyms and exclude common elements from indexes (e.g. "a" and "the" from English text), amongst other things.

https://www.postgresql.org/docs/current/static/textsearch-dictionaries.html

Upvotes: 0

Related Questions