2

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 + '%'
    }
}});
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
Jagadish Kamath
  • 103
  • 2
  • 8
  • You're doing `"apple" LIKE "apple_shake%"`, but you want the reverse: `"apple_shake" LIKE "apple"`. Though I don't yet know how to express this with `sequelize.js`. – alx Jun 22 '19 at 15:43
  • Precisely! I could do it through raw query. But I'm not able to make that a query through sequelize model. Perhaps I should use the query directly instead of relying on model? – Jagadish Kamath Jun 22 '19 at 16:04
  • Raw query should work. Also, I think you can express this using `LOCATE()` MySQL function -- `sequelize.js` supports functions, and I think you can express this as part of your `where:` expression in the code above. For `COUNT()` example would look like this: `sequelize.fn('COUNT', sequelize.col('table_column_name'))`, and you probably can extend this to work with `LOCATE()`. But I'm guessing here, no real experience with this library. – alx Jun 22 '19 at 16:09
  • This not a problem with Sequelize, this type of query is not possible with SQL – doublesharp Jun 22 '19 at 16:18
  • @alx LOCATE() is like indexof, it's not going to work for this example. You would have to decompose the input and iteratively query which is not going to be very efficient. If you can do something like split the input on `_` then you would be able to use a regular LIKE. – doublesharp Jun 22 '19 at 16:20
  • 1
    @doublesharp I don't understand why you say it's impossible, look at this sqlfiddle: http://www.sqlfiddle.com/#!9/4c8192/2. I also have concerns re efficiency, but not about possibility as such. – alx Jun 22 '19 at 16:25
  • @alx i stand corrected - I didn't know you could reverse the inputs like that and have it work. I do wonder about the efficiency with a larger dataset though. – doublesharp Jun 22 '19 at 16:42

3 Answers3

3

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;
doublesharp
  • 26,888
  • 6
  • 52
  • 73
  • Great! Works like a charm. Not sure about the performance though. And I never knew it was this complicated. Finding a superstring is straight forward but finding a substring is quite hard – Jagadish Kamath Jun 22 '19 at 19:17
2

Sequelize has a substring operator which you could use directly to solve this.

var sample_fruit_string = "apple_shake";
var gsp_config  = await model.fruit.findOne({where: {
    fruit_name: {
        [Op.substring]: sample_fruit_string // LIKE '%sample_fruit_string%'
    }
}});
7hibault
  • 2,371
  • 3
  • 23
  • 33
0
var sample_fruit_string = "apple_shake";
var gsp_config  = await model.fruit.findOne({where: {
fruit_name: {
    [Op.like]: `%${sample_fruit_string}%` // LIKE '%sample_fruit_string%'
   //  [Op.ilike]: `%${sample_fruit_string}%` // For case sensitive searching
   // [Op.substring]: sample_fruit_substring  // Has been depreciated in future version of sequelize.
}
}});
varun verma
  • 1
  • 1