Sequelize:查找所有匹配包含的内容(不区分大小写) [英] Sequelize: Find All That Match Contains (Case Insensitive)

问题描述

我想使用 sequelize.js 来查询模型中包含约束的记录.我该怎么做?

I want to use sequelize.js to query a model for records with a contains restraint. How do I do that?

这就是我现在所拥有的:

This is what I have right now:

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 中使用 contains 查询?

How do you use a contains query in sequelize?

推荐答案

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 查询看起来像那样

In order to make it case insensitive, you could use the LOWER sql function, but previously you would also have to lower case your request.body.query value. Sequelize query would then look like that

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 值小写.在这种情况下，您比较两个小写的字符串.

What it does is to lower case your asset_name value from table, as well as lower case the request.body.query value. In such a case you compare two lower cased strings.

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

In order to understand better what is happening in this case I recommend you take a look at the sequelize documentation concerning sequelize.where(), sequelize.fn() as well as sequelize.col(). Those functions are very useful when trying to perform some unusual queries rather than simple findAll or findOne.

这种情况下的 sequelize 当然是你的 Sequelize 实例.

The sequelize in this case is of course your Sequelize instance.

这篇关于Sequelize:查找所有匹配包含的内容(不区分大小写)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！