MySQL FullText Search with Sequelize

Question

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 !

Montymonument

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

Malady

Answer 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.

Frechette

Yes, It seems Sequelize does not support Full Text Search beyond creating it's Index. I have used Query API to perform the Full Text Query and is working with a limitation that synonyms, plurals, inflectional word (run, runs, running / lie, lying etc) are not supported. Can you please let me know how I can add support to this in Full Text Search Query ? – Montymonument

What is Query API? Do you mean 'Query Expansion' from MySQL docs? – Frechette

The sequelize "query" API, where we can pass the SQL query. Following is code snippet: sequelize .query(SELECT * FROM table_name WHERE MATCH (term) AGAINST ("teen" IN NATURAL LANGUAGE MODE), { model: VolumeModel }).then(function(){}) – Montymonument

I don't think the replacement form of the where clause as shown in this example is still valid. Looks to be deprecated in recent versions: Unhandled rejection Error: Support for literal replacements in the where object has been removed. – Overshoe

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

Schellens

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

Cauterize

Answer 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
    ),
  },
})

Collage