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.

patricus · Accepted Answer · 2015-04-27 07:41:35Z

13

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

answered Apr 27, 2015 at 7:41

patricus

62.9k15 gold badges157 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cosmoloc Over a year ago

Thanks a lot patricus..can't believe missed that

ibnɘꟻ · Accepted Answer · 2021-06-16 02:30:05Z

7

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

Comments

Herman Demsong · Accepted Answer · 2018-07-28 11:14:30Z

4

Using ILIKE instead of LIKE solved it for me

answered Jul 28, 2018 at 11:14

Herman Demsong

3513 silver badges7 bronze badges

Collectives™ on Stack Overflow

Laravel postgres sql Case Insensitive Like

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related