CODEWITHSUNDEEP
mysqldatabasesequelize.js
TIMEX
TIMEX

Reputation: 272274

Sequelize Op.iLike throwing Error with MySQL

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.

Upvotes: 3

Views: 1567

Answers (1)

nbk
nbk

Reputation: 49410

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)

Upvotes: 4

Related Questions