Laravel postgres sql 不区分大小写喜欢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29888874/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel postgres sql Case Insensitive Like
提问by zeetit
I have a postgres sql query in Laravel :
我在 Laravel 中有一个 postgres 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();
Output/Error: 'PDOException' with message 'SQLSTATE[42P18]: Indeterminate datatype: 7 ERROR: could not determine data type of parameter $2'
输出/错误：带有消息“SQLSTATE[42P18]”的“PDOException”：不确定数据类型：7 错误：无法确定参数 $2 的数据类型
Tried Query builder :
尝试查询生成器：
$_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();
Output : Invalid FROM.. table not found
输出 : Invalid FROM.. table not found
Tried ILike (Based on a similar question without a join)
尝试过 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']) ) );
Output : Empty array
输出：空数组
Tried :
尝试过：
$_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']) ) );
});
Output/Error: 'PDOException' with message 'SQLSTATE[42P18]: Indeterminate datatype: 7 ERROR: could not determine data type of parameter $2'
输出/错误：带有消息“SQLSTATE[42P18]”的“PDOException”：不确定数据类型：7 错误：无法确定参数 $2 的数据类型
I have to make a case-insensitive search query based on the input parameter.
我必须根据输入参数进行不区分大小写的搜索查询。
回答by patricus
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 should be:
查询应该是：
$_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']).'%');
}
回答by Herman Demsong
Using ILIKEinstead of LIKEsolved it for me
使用ILIKE而不是LIKE为我解决它
