MySQL FullText Search with Sequelize

Since we now have the error message in recent Sequelize that looks like this:

Unhandled rejection Error: Support for literal replacements in the where object has been removed.

The solution would be to provide replacements manually

Payments.findAll({
  where: Sequelize.literal('MATCH (SomeField) AGAINST (:name)'),
  replacements: {
    name: 'Alex'
  }
});

Use arrays for more complex conditions:

Payments.findAll({
  where: [
    { State: 'Paid' },
    Sequelize.literal('MATCH (SomeField) AGAINST (:name)')
  ],
  replacements: {
    name: 'Alex'
  }
});

Sequelize doesn’t fully support the full-text search feature. We can add a FULLTEXT index as easy as any other index. But operators supporting the MATCH (column) AGAINST (value) syntax haven’t been implemented.

My current solution to the problem consists of creating a regular model:

module.exports = (sequelize, DataTypes) => {
  const Book = sequelize.define('Book', {
    title: DataTypes.STRING,
    description: DataTypes.TEXT,
    isActive: DataTypes.BOOLEAN
  }, {
    indexes: [
      // add a FULLTEXT index
      { type: 'FULLTEXT', name: 'text_idx', fields: ['description'] }
    ]
  });

  return Book;
};

And using a raw query for querying:

const against = 'more or less';

models.Book.find({
  where: ['isActive = 1 AND MATCH (description) AGAINST(?)', [against]]
}).then((result) => {
  console.log(result.title);
});

Using only MySQL it's not possible to get correct results if you trying to search for inflectional words, synonyms etc. MySQL developers consider adding dictionaries for full-text search (https://dev.mysql.com/worklog/task/?id=2428), but who knows when we will see it.

If you have to stick with MySQL, I suggest to take a look at Sphinx. It works properly with synonyms and inflectional words.

Since Sequlize does not support for fullText search.

Here is another approach for searching a string from a table

models.sequelize.query(
    "SELECT * FROM tableName WHERE CONCAT(field1, '', field2, '', field3, '', field4 ) LIKE \"%" + keyword + "%\"",
    {type: models.sequelize.QueryTypes.SELECT}).then(res => {
    
})

MySQL FullText Search with Sequelize

Here is another approach for searching a string from a table

Related

Recent Posts