javascript - Sequelize : Find All That Match Contains (Case Insensitive)

Question

我想使用 sequelize.js 查询模型以获取包含约束的记录。我该怎么做？

这是我现在拥有的:

Assets
  .findAll({ limit: 10, where: ["asset_name like ?", '%' + request.body.query + '%'] })
  .then(function(assets){
    return response.json({
      msg: 'search results',
      assets: assets
    });
  })
  .catch(function(error){
    console.log(error);
  });

但我收到以下错误:

   { error: operator does not exist: character varying @> unknown
       at Connection.parseE (/home/travellr/safe-star.com/SafeStar/node_modules/pg/lib/connection.js:554:11)
       at Connection.parseMessage (/home/travellr/safe-star.com/SafeStar/node_modules/pg/lib/connection.js:381:17)
       at Socket.<anonymous> (/home/travellr/safe-star.com/SafeStar/node_modules/pg/lib/connection.js:117:22)
       at emitOne (events.js:96:13)
       at Socket.emit (events.js:188:7)
       at readableAddChunk (_stream_readable.js:176:18)
       at Socket.Readable.push (_stream_readable.js:134:10)
       at TCP.onread (net.js:548:20)
     name: 'error',
     length: 209,
     severity: 'ERROR',
     code: '42883',
     detail: undefined,
     hint: 'No operator matches the given name and argument type(s). You might need to add explicit type casts.',
     position: '246',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'parse_oper.c',
     line: '722',
     routine: 'op_error',
     sql: 'SELECT "id", "asset_name", "asset_code", "asset_icon", "asset_background", "asset_add_view", "asset_add_script", "asset_add_id_regex", "date_created", "uniqueValue", "createdAt", "updatedAt" FROM "assets" AS "assets" WHERE "assets"."asset_name" @> \'%a%\' LIMIT 10;' },
  sql: 'SELECT "id", "asset_name", "asset_code", "asset_icon", "asset_background", "asset_add_view", "asset_add_script", "asset_add_id_regex", "date_created", "uniqueValue", "createdAt", "updatedAt" FROM "assets" AS "assets" WHERE "assets"."asset_name" @> \'%a%\' LIMIT 10;' }

如何在 sequelize 中使用包含查询？

Answer 1

Assets.findAll({
        limit: 10,
        where: {
            asset_name: {
                [Op.like]: '%' + request.body.query + '%'
            }
        }
}).then(function(assets){
    return response.json({
        msg: 'search results',
        assets: assets
    });
}).catch(function(error){
    console.log(error);
});

编辑

为了使其不区分大小写，您可以使用 LOWER sql 函数，但以前您还必须将 request.body.query 值小写。 Sequelize 查询将看起来像那样

let lookupValue = request.body.query.toLowerCase();

Assets.findAll({
    limit: 10,
    where: {
        asset_name: sequelize.where(sequelize.fn('LOWER', sequelize.col('asset_name')), 'LIKE', '%' + lookupValue + '%')
    }
}).then(function(assets){
    return response.json({
        msg: 'message',
        assets: assets
    });
}).catch(function(error){
    console.log(error);
});

它所做的是将表中的 asset_name 值和 request.body.query 值小写。在这种情况下，您比较两个小写字符串。

为了更好地理解这种情况下发生了什么，我建议您查看有关 sequelize.where() 的 Sequelize 文档。 , sequelize.fn()以及sequelize.col() .当尝试执行一些不寻常的查询而不是简单的 findAll 或 findOne 时，这些函数非常有用。

本例中的 sequelize 当然是您的 Sequelize 实例。