How to make case insensitive search in laravel table ?

Case insensitive search refers to a search in which upper case or lower case charaters in the search string doesn't affect the outcome of the search. We need such type of search when we have large data set and we want quality output from search.

In laravel, we can make simple search with where clauses and then we can paginate the result using simple paginate function. For case insensitive search, we have to execute raw database queries along with php case functions.

Lets consider  we have a posts table with following structure.

Column Type
ID Integer(Primary Key)
Title String
Summary Text
Content Long Text
Created At Timestamp
Updated At Timestamp
Why we need case insensitive search ?

We can search this table using simple where clauses and paginate the results like this:

$posts = DB::table('posts')
                ->where('title', 'LIKE', '%'.$keyword.'%')
                ->orWhere('summary', 'LIKE', '%'.$keyword.'%')
                ->orWhere('content', 'LIKE', '%'.$keyword.'%')
                ->paginate();

We can display page links like:

{{$posts->appends(['keyword' => $keyword])->links()}}

In above example, we have made a search to match keyword with title, summary and content column of posts table. After that, we have appended keyword query string with its value in each pagination links.

Also Read: HTTP Exception And Error Handling In Laravel

In this example, search is case sensitive which means we will have different outcomes for same word with different case. We might get different results if we take two search keywords like "Pagination" and "pagination". In order to prevent such issues and to make search more efficient we need case insensitive search.

Solution

If you are using postgres db, then you can replace like operator by ilike operator which means insensitive case. Your query look like:

$posts = DB::table('posts')
                ->where('title', 'ilike', '%'.$keyword.'%')
                ->orWhere('summary', 'ilike', '%'.$keyword.'%')
                ->orWhere('content', 'ilike', '%'.$keyword.'%')
                ->paginate();

In order database supported by larave, ilike operator doesn't work. Don't worry about this because we have another way for other databases.

We can easily make case insensitive search using raw database queries and strtoupper php function.

Algorithm
  1. Upper Case the search keyword
  2. Upper Case the database column values
  3. Match keyword with database values and get results

Now, Our query will look like:

$posts = DB::table('posts')
            ->where(function ($query) use ($keyword){
                    $query->whereRaw('UPPER(title) Like ?','%'.strtoupper($keyword).'%')
                   ->orwhereRaw('UPPER(summary) Like ?', '%'.strtoupper($keyword).'%')
                   ->orwhereRaw('UPPER(content) Like ?','%'.strtoupper($keyword).'%');
            })
           ->paginate();

We will append query parameter keyword to each page links in pagination using appends function.

{{$posts->appends(['keyword' => $keyword])->links()}}

Now, we can go through pages of the search result. It's preety simple to make case insensitive search, isn't it ?


share :

Sagar Gautam

A Computer Engineer from Nepal.


Leave your Feedback