Laravel与Postgres SQL不区分大小写的Like匹配

Question

我在Laravel中有一个PostgreSQL SQL查询：

$_query = Article::join('users', 'articles.user_id', '=', 'users.id')
                    ->select('users.*','articles.*');                           
if( array_key_exists('title', $parameters) && $parameters['title'] != '' )       
        $_query->whereRaw( " LOWER(nbl_region_ref.region) like LOWER('%?%')", array( trim($parameters['region']) ) );
$result = $_query->get();

输出/错误信息：'PDOException'，消息为'SQLSTATE[42P18]：不确定数据类型：7 ERROR：无法确定参数$2的数据类型'

尝试使用查询构建器：

$_query= DB::select("select users.*, articles.* from articles")
                ->join('users', 'articles.user_id', '=', 'users.id');
if( array_key_exists('title', $parameters) && $parameters['title'] != '' )       
            $_query->where( "articles.title","ILIKE", array( trim($parameters['title']) ) );
    $result = $_query->get();

输出: 无效的 FROM .. 表不存在

尝试使用 ILike（基于一个没有连接的类似问题）

$_query = Article::join('users', 'articles.user_id', '=', 'users.id')
                ->select('users.*','articles.*');                           
if( array_key_exists('title', $parameters) && $parameters['title'] != '' )       
                $_query->where( "articles.title","ILIKE", array( trim($parameters['title']) ) );

输出：空数组

尝试：

 $_query = Article::join('users', 'articles.user_id', '=', 'users.id')
                        ->select('users.*','articles.*');                           

$_query->where( function ( $_queryTemp ) use ( $parameters ) {
if( array_key_exists('title', $parameters) && $parameters['title'] != '' )       
            $_query->whereRaw( " LOWER(nbl_region_ref.region) like LOWER('%?%')", array( trim($parameters['region']) ) );
});

输出/错误：'PDOException'，错误信息为'SQLSTATE[42P18]：不确定的数据类型：7 错误：无法确定参数$2的数据类型'

我需要根据输入参数进行不区分大小写的搜索查询。

- cosmoloc

Answer 1

你的第三次尝试看起来就像你想要的。根据你的第一次和最后一次尝试，似乎你想让搜索文本被包裹在“%”中。由于在第三次尝试中没有这样做，我认为这就是你的查询没有找到任何结果（空数组）的原因。

查询应该是：

$_query = Article::join('users', 'articles.user_id', '=', 'users.id')
    ->select('users.*','articles.*');
if (array_key_exists('title', $parameters) && $parameters['title'] != '') {
    $_query->where('articles.title', 'ILIKE', '%'.trim($parameters['title']).'%');
}

- patricus

---

非常感谢你，Patricus。我简直不敢相信我错过了那个。 - cosmoloc

这也帮了我大忙。谢谢@patricus。 - Zaid Haider

Answer 2

我使用ILIKE代替LIKE，问题得到了解决。

- Herman Demsong

Answer 3

与MySQL不同，PostgreSQL提供了两个模式匹配选项：ILIKE 和 LIKE。

• ILIKE是不区分大小写的
• LIKE是区分大小写的

如果你想按照不区分大小写的值查询，就像title一样，那么你应该使用ILIKE。

// app/Models/Article.php
public static function getByTitle($title){
    return self::where('title', 'ilike', "%${title}%")->orderBy('title', 'asc')->get();
}

更多关于PostgreSQL文档的内容：

ILIKE关键字可以代替LIKE，根据当前区域设置使匹配不区分大小写。这不属于SQL标准，而是PostgreSQL的扩展功能。

https://www.postgresql.org/docs/8.3/functions-matching.html

- ibnɘꟻ