Sequelize Op.iLike throwing Error with MySQL

user_179736 0
Viewed 304

Error: Invalid value { undefined: 'w%' }

This is my query:

  results = await models.Record.findAll({
          where: {
            
            name: {
              [Op.iLike]: prefix + "%", //causing problems
            },
          },
          order: [["createdAt", "DESC"]],
          limit: num,
        });

name is a String field in my MySQL table.

You have an error in your SQL syntax near 'ILIKE' 'james'. It seems the ORM converts the query to ILIKE, which is not valid.

sequelize.js mysql database
1 Answers

sequelize should work with ILike for mysql see link at the end

you ca use instead Op.startsWith

    results = await models.Record.findAll({
      where: {
        
        name: {
          [Op.startsWith]: prefix, 
        },
      },
      order: [["createdAt", "DESC"]],
      limit: num,
    });

Further string functions see manual

  [Op.like]: '%hat',                       // LIKE '%hat'
  [Op.notLike]: '%hat',                    // NOT LIKE '%hat'
  [Op.startsWith]: 'hat',                  // LIKE 'hat%'
  [Op.endsWith]: 'hat',                    // LIKE '%hat'
  [Op.substring]: 'hat',                   // LIKE '%hat%'
  [Op.iLike]: '%hat',                      // ILIKE '%hat' (case insensitive) (PG only)
  [Op.notILike]: '%hat',                   // NOT ILIKE '%hat'  (PG only)
  [Op.regexp]: '^[h|a|t]',                 // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
  [Op.notRegexp]: '^[h|a|t]',              // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
  [Op.iRegexp]: '^[h|a|t]',                // ~* '^[h|a|t]' (PG only)
  [Op.notIRegexp]: '^[h|a|t]',             // !~* '^[h|a|t]' (PG only)
user_51935360
Related