'MySQL FullText Search with Sequelize

I want to implement MySQL full text search with sequelize. The version "sequelize": "^3.23.6". I tried to research about this but could not find =the documentation that guides how to implement this. Here is the link that says FullText is supported by sequelize: https://github.com/sequelize/sequelize/issues/2979

But there is not exact documentation on how to do it and how to do a full text search query with sequelize.

Any links advice would be helpful

Thanks !

mysqlfull-text-searchsequelize.js


Solution 1:[1]

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'
  }
});

Solution 2:[2]

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.

Solution 3:[3]

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 => {
    
})

Solution 4:[4]

another approach, using single migration file

module.exports = {
  up: (queryInterface, Sequelize) => queryInterface.createTable(
    'Products',
    {
      id: {
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4,
        allowNull: false,
        primaryKey: true,
      },
      name: {
        type: Sequelize.STRING,
      },
    },
  ).then(() => queryInterface.addIndex('Products', ['name'], { type: 'FULLTEXT' })),
  down: (queryInterface) => queryInterface.dropTable('Products'),
};

Solution 5:[5]

New updated Answer, Here I am searching for Locations that match my searchString.

 Location.findAll({
  where: {
    name: sequelize.where(
      sequelize.fn("LOWER", sequelize.col("name")),
      "LIKE",
      "%" + "You search text here"+ "%".   <==== add your searchString Here
    ),
  },
})

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Alex K
Solution 2
Solution 3 Community
Solution 4 Muhammad Ibrahim
Solution 5 Muhammad Haidar

Related Questions