Symfony2 Doctrine - PostgreSQL 的 ILIKE 子句? [英] Symfony2 Doctrine - ILIKE clause for PostgreSQL?

问题描述

我目前正在使用 symfony2、docter 2.3 和 PostgreSQL 9.我已经搜索了几个小时，看看我到底如何使用 QueryBuilder 执行 ILIKE 选择.

I'm currently using symfony2, doctrine 2.3 and PostgreSQL 9. I've been searching for a couple of hours now to see HOW on earth do I do a ILIKE select with QueryBuilder.

似乎他们只有LIKE.不过，在我的情况下，我正在搜索不区分大小写的.它到底是怎么做的?

It seems they only have LIKE. In my situation, though, I'm searching case-insensitive. How on earth is it done?

// -- this is the "like";
$search = 'user';
$query = $this->createQueryBuilder('users');
$query->where($query->expr()->like('users.username', $query->expr()->literal('%:username%')))->setParameter(':username', $search);


// -- this is where I get "[Syntax Error] line 0, col 86: Error: Expected =, <, <=, <>, >, >=, !=, got 'ILIKE'
$search = 'user';
$query = $this->createQueryBuilder('users');
$query->where('users.username ILIKE :username')->setParameter(':username', $search);

推荐答案

我不知道 Symfony，但你可以替代

I don't know about Symfony, but you can substitute

a ILIKE b

与

lower(a) LIKE lower(b)

你也可以试试运算符~~*，它是ILIKE的同义词它的运算符优先级略低，因此您可能需要为连接字符串加上括号，而ILIKE

You could also try the operator ~~*, which is a synonym for ILIKE It has slightly lower operator precedence, so you might need parenthesis for concatenated strings where you wouldn't with ILIKE

a ILIKE b || c

变成

a ~~* (b || c)

关于模式匹配的手册，以<开头code>LIKE/ILIKE.

我想这家伙有同样的问题并得到了答案:
http://forum.symfony-project.org/viewtopic.php?f=23&t=40424

I think this guy had the same problem and got an answer:
http://forum.symfony-project.org/viewtopic.php?f=23&t=40424

显然，您可以使用 SQL 供应商特定的功能扩展 Symfony2:
http://docs.doctrine-project.org/projects/doctrine-orm/en/2.1/cookbook/dql-user-defined-functions.html

Obviously, you can extend Symfony2 with SQL vendor specific functions:
http://docs.doctrine-project.org/projects/doctrine-orm/en/2.1/cookbook/dql-user-defined-functions.html

我不喜欢 ORM 和框架，为了保持可移植性"而扼杀 Postgres 的丰富功能；(这几乎不起作用).

I am not a fan of ORMs and frameworks butchering the rich functionality of Postgres just to stay "portable" (which hardly ever works).

这篇关于Symfony2 Doctrine - PostgreSQL 的 ILIKE 子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！