[SOLVED] Sequelize : How to check if a substring exists in a table

Sequelize : How to check if a substring exists in a table

Is there a way to check if a particular string exists in a column in a table?

For example, I have a table 'fruits' with two columns, primary key and fruit_name and following rows

1 apple
2 orange
3 pineapple

I have a sample string named apple_shake. I need to check if a substring of this apple_shake exists. The query should return row containing 'apple'

I know how this can be done in mysql query - SQL - Query to find if a string contains part of the value in Column

But through sequelize, following has problem

var sample_fruit_string = "apple_shake";
var gsp_config  = await model.fruit.findOne({where: {
    fruit_name: {
        [Op.like]: sample_fruit_string + '%'
    }
}});

Solution

Credit to @alx for the SQL I didn't know was possible - this is how you generate the appropriate SQL with Sequelize. Note that this may not be efficient with large datasets.

const sample_fruit_string = "apple_shake"; 

const gsp_config = await model.fruit.findOne({
  attributes: ['id'],
  where: Sequelize.where(
      Sequelize.fn('LOCATE', Sequelize.col('fruit_name'), sample_fruit_string),
      Sequelize.Op.ne,
      0
  ),
  logging: true,
});

Generates the following:

SELECT `id` FROM `fruit` AS `fruit` WHERE LOCATE(`fruit_name`, 'apple_shake') != 0 LIMIT 1;