如何在Sequelize Postgres中使用小写函数

我正在尝试使用小写函数在Sequelize中进行stringsearch。 我设法使用ilike来做。 我的问题是如何在这种情况下使用小写函数?

findAll使用ilike如下所示:

 Db.models.Person.findAll(where: {firstName: {$ilike: `somename`}}); 

如何将其更改为lower(firstname) = lower('somename');

       

网上收集的解决方案 "如何在Sequelize Postgres中使用小写函数"

PostgreSQL通常使用区分大小写的sorting规则。 这意味着每个列中显示的数据将与您的查询进行字面比较。

你有几个select:


1.按照Ben的回答,在sequelize.fn('lower')调用中包装你的包装列和数据库。

优点:没有数据库更改。

缺点:您需要记住为每个查询使用它。 前面的索引(除非你已经创build了一个函数索引 ),并顺序扫描表,导致大表查找速度较慢。 相当详细的代码。


2.使用ILIKE,以区分大小写匹配模式

要准确find名称:

Db.models.Person.findAll(where: {firstName: {$iLike: 'name'}});

要查找可能包含在任意字符中的片段:

Db.models.Person.findAll(where: {firstName: {$iLike: '%name%'}});

优点:易于记忆。 没有Sequelize函数包装 – 这是一个内置的运算符,所以语法更简洁。 不需要特殊的索引或数据库更改。

缺点:慢,除非你开始搞乱像pg_trgm这样的扩展


3.用citexttypes定义你的文本列,它隐式地比较小写字母

将列types定义为“citext”(而不是textcharacter varying )具有相同的实际效果:

select * from people where name = 'DAVID'

这个…

select * from people where LOWER(name) = LOWER('DAVID')

PostgreSQL文档将其作为如何使用citexttypes创build表的示例:

 CREATE TABLE users ( nick CITEXT PRIMARY KEY, pass TEXT NOT NULL ); INSERT INTO users VALUES ( 'larry', md5(random()::text) ); INSERT INTO users VALUES ( 'Tom', md5(random()::text) ); INSERT INTO users VALUES ( 'Damian', md5(random()::text) ); INSERT INTO users VALUES ( 'NEAL', md5(random()::text) ); INSERT INTO users VALUES ( 'Bjørn', md5(random()::text) ); SELECT * FROM users WHERE nick = 'Larry'; 

TL; DR基本上把你的“文本”列换成“citext”。

citext模块捆绑了PostgreSQL 8.4,所以不需要安装任何扩展。 但是,您需要在使用以下SQL的每个数据库上启用它:

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;

然后在你的Sequelize定义中,定义一个type属性:

 // Assuming `Conn` is a new Sequelize instance const Person = Conn.define('person', { firstName: { allowNull: false, type: 'citext' // <-- this is the only change } }); 

然后,针对该列的search将始终不区分大小写,并使用常规的where =查询

优点:无需在丑陋的sequelize.fn调用中包装您的查询。 不需要记住显式小写。 区域识别,所以跨所有字符集。

缺点:首先定义表格时,需要记住在Sequelize定义中使用它。 始终激活 – 您需要知道在定义表格时,您需要执行不区分大小写的search。

你可以在where子句中使用本地函数:

 Db.models.Person.findAll(where: sequelize.where(sequelize.fn('lower', sequelize.col('firstname')), sequelize.fn('lower', 'somename')); 

这将转化为

 select * from person where lower(firstname) = lower('somename');