Laravel postgres sql Case Insensitive Like

Question

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();

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

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']) ) );

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'

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

Answer 1

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

Answer 2

Unlike MySQL, PostgreSQL has two options for pattern matching: ILIKE and LIKE.

• ILIKE is for case in-sensitive
• LIKE is for case sensitive

If you want to query based on in-sensitive value, like title. You should use ILIKE.

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

More on PostgreSQL docs :

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

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