Advance Searching in Laravel for JSON Column type and using WHEN Method

As a Regular Programmer Advance Searching is something you encounter once in a while. I recently implemented it in Laravel. Which i am going to explain here.

Prerequisite - it’s assumed that you do have basic knowledge of Laravel Architecture and its functionality such as how model, controller and routing works.

Okay so we do have a biodata table as the name represents it saves the biodata of a user. And this is how its migration looks like

Migration

$table->id();
$table->enum('gender',['boy','girl']);
$table->string('name',255);
$table->json('biodata');
$table->timestamps();

As you can see, we do have a table column biodata which is JSON type. This allows us to save JSON in our db. And we are going to use attribute casting to convert it to object automatically when fetching. So our Biodata model would look like this

Model

namespace App;

use Illuminate\Database\Eloquent\Model;

class Biodata extends Model
{
    protected $guarded = [];

    protected $casts = [
       'biodata' => object
    ];

    protected $perPage = 10;

}

Now before going further let’s see what our Biodata object would look like

Factory

return [
            'gender' => $this->faker->randomElement($this->gender),
            'name'=> $this->faker->name(),
            'biodata' => [
                'personal'=>[
                    'qualification' => $this->faker->randomElement($this->qualification),
                    'height'=>$this->faker->randomElement($this->height),
                    'income'=>$this->faker->numberBetween(100000,1000000),
                    'occupation' => $this->faker->randomElement($this->jobs),
                ],
                'family'=>[
                    'father_gotra' => $this->faker->randomElement($this->gotra),
                    'mother_gotra' => $this->faker->randomElement($this->gotra),
                ],
                'contact'=>[
                    'state' => $this->faker->randomElement($this->indianStates),
                ]
            ],
        ];

And on all these fields we are going to run a search.
Now It’s time to discuss what we want to get out of our search function. So here our all optional search parameters

• Gender
  • Single Value
  • field type - ENUM
• Qualification
  • Could be Multiple Values
  • field type - JSON Key
• Min Height
  • Single Value
  • field type - JSON Key
• Income
  • Single Value
  • field type - JSON Key
• Occupation
  • Single Value
  • field type - JSON Key
• Gotra
  • Multiple Value
  • field type - JSON Key

Now this might seem very straightforward to you, all you have to use is where statement and you should be okay BUT hold your horses and Let’s go over some obvious problems first

Searching in JSON field ?

So how are we going to search in JSON ? well apparently that's not hard with MYSQL and even better with Laravel, in nutshell this is how you search in a JSON field provided its key

return $query->where('biodata->personal->income', '>=', $minIncome);

Check Biodata object above to understand.

Multiple Values

Now before jumping into code to write search function, the one question you might be asking is how you are supposed to send multiple values in a query string ? well glad you asked, we will simply add them with comma like this

http:://url.com/?qualification=MBA,BA&gender=boy 

Now we can simply explode them using “,” and it will give us an array of values for that parameter.

How to add multiple queries to where statements ?

So this is a standard where statement from Laravel docs

DB::table('users')->where('votes', 100)->get();

And we do have a better one too

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

Here you can add multiple arguments to where statement,
But We need to have some kind of validation before we actually jump to running any query, Because all parameters are optional, so user might not provide all or any input to add to query.

And hence we need to write extra code when searching with where and that’s where when comes to save us. This is what official documentation says about it

Sometimes you may want clauses to apply to a query only when something else is true. For instance you may only want to apply a where statement if a given input value is present on the incoming request. You may accomplish this using the when method:

And this is how you write it

$users = DB::table('users')
                ->when($role, function ($query, $role) {
                    return $query->where('role_id', $role);
                })
                ->get();

But we have one more problem, how do we go about adding multiple values for the same parameter, well the second argument of when is a function and we can do anything in this function :) keep reading

So for checking multiple values for the same parameter we are going to use a loop in it and this is how it's going to look like.

->when($gotra, function ($query, $gotra) {
               foreach($gotra as $g){
                   $query->where('biodata->family->father_gotra', '<>' ,$g);
               }
               return $query;
           })

So that’s it. we covered all the basics we going to need to write search function. and this is how it’s going to look like

public function search(){

       $gender = request('gender');
       $state = request('state');

       $minIncome = (int)request('minIncome');
       $minHeight = (int)request('minHeight');

       $qualifications = request('qualification') != '' ? explode(",", request('qualification') ) : false;

       $gotra = request('gotra') != '' ? explode(",", request('gotra') ) : false;

       $results = Biodata::
           when($gender, function ($query, $gender) {
               return $query->where('gender', $gender);
           })
           ->when($state, function ($query, $state) {
               return $query->where('biodata->contact->state', $state);
           })
           ->when($qualifications, function ($query, $minQualification) {

               foreach($qualifications as $qualification){
                   $query->where('biodata->personal->qualification', '=', $qualification);
               }
               return $query;

           })
           ->when($minIncome, function ($query, $minIncome) {
               return $query->where('biodata->personal->income', '>=', $minIncome);
           })
           ->when($minHeight, function ($query, $minHeight) {
               return $query->where('biodata->personal->height', '>=' , $minHeight);
           })
           ->when($gotra, function ($query, $gotra) {
               foreach($gotra as $g){
                   $query->where('biodata->family->father_gotra', '<>' ,$g);
               }
               return $query;
           })
           ->paginate(10);

          return response($results,200);

I followed TDD approach while developing this so i created tests first, let me know if anyone of you want to check them out. Send me msg and i will share code.

So i will leave you guys with this today, let me know what you think.