如何在laravel 5.4中将多个范围添加到DB :: table

Question

I am new on laravel 5, I was trying to add multiples scopes like this:

$projects = DB::table('projects AS p')->select('p.project_name','c.name_country', 'p.date_project','s.name_status','p.rejected_date')                    
                    ->join('country AS c','c.id','=','p.id_country')
                    ->join('status AS s','s.id','=','p.id_status')
                    ->where('p.active','=','1');  

$projects = $this->scopeCountry($projects , $country);
$projects = $this->scopeStatus($projects , $status);  
$projects->get(); 
return view('search.ajax')->with('projects', $projects);
}
    public function scopeCountry($query, $country){
            return is_null($country) ? $query : $query->where('p.id_country','=',$country);
        }

public function scopeStatus($query, $status){
            return is_null($status) ? $query : $query->where('p.id_status','=',$status);
        }
}

It gave me this error: ErrorException in 26864233b0cd26a1055b66c2645628be451523fd.php line 4: Undefined property: Illuminate\Database\MySqlConnection::$project_name (View:/home/vagrant/Code/projects/resources/views/search/ajax.blade.php)

I noticed that this error doesn't show up if I modify it like this:

//$projects = $this->scopeStatus($status);  
//$projects->get(); 

And then I add the get() function in:

public function scopeCountry($query, $country){
                return is_null($country) ? $query->get() : $query->where('p.id_country','=',$country)->get();
            }

The reason why I don't add the queries in one scope and I use is_null($country), is because in case those variables($country, $status) were null only the main query should be executed:

$projects = DB::table('projects AS p')->select('p.project_name','c.name_country', 'p.date_project','s.name_status','p.rejected_date')                    
                        ->join('country AS c','c.id','=','p.id_country')
                        ->join('status AS s','s.id','=','p.id_status')
                        ->where('p.active','=','1'); 

And also I have to add similar scopes to another fields, maybe there is another way to do it, I am not sure. Thank you.

Answer 1

I just found out an easier solution using the when method:

$projects = DB::table('projects AS p')->select('p.project_name','c.name_country', 'p.date_project','s.name_status','p.rejected_date')                    
                        ->join('country AS c','c.id','=','p.id_country')
                        ->join('status AS s','s.id','=','p.id_status')
                        ->where('p.active','=','1') 
                        ->when($country, function($query) use ($country){
                             return $query->where('p.id_country','=',$country);
                        })
                        ->when($status, function($query) use ($status){
                             return $query->where('p.id_status','=',$status);
                        }); 

When the $country or $status variable is not present or null, what it is inside the closure doesn't execute.