Laravel Postgres sql不区分大小写喜欢 [英] Laravel postgres sql Case Insensitive Like

问题描述

我在Laravel中有一个postgres sql查询:

I have a postgres sql query in Laravel :

$_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错误:无法确定参数$ 2的数据类型"

Output/Error: 'PDOException' with message 'SQLSTATE[42P18]: Indeterminate datatype: 7 ERROR: could not determine data type of parameter $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 ..表

Output : Invalid FROM.. table not found

尝试过ILike(基于类似的问题，但没有加入)

Tried ILike (Based on a similar question without a join)

$_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的数据类型"

Output/Error: 'PDOException' with message 'SQLSTATE[42P18]: Indeterminate datatype: 7 ERROR: could not determine data type of parameter $2'

我必须根据输入参数进行不区分大小写的搜索查询.

I have to make a case-insensitive search query based on the input parameter.

推荐答案

您的第三次尝试看起来像您想要的.根据您的首次尝试和最后一次尝试，您似乎希望将搜索文本括在'％'中.由于您没有第三次尝试这样做，因此我假设这就是您的查询未找到任何结果(空数组)的原因.

Your third attempt looks like what you want. Based on your first and last attempt, it looks like you want your search text wrapped in '%'. Since you didn't do this for your third attempt, I'm assuming that's why your query didn't find any results (empty array).

查询应为:

$_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']).'%');
}

这篇关于Laravel Postgres sql不区分大小写喜欢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！