laravel join query where AND OR both cluse

replied 7 years ago

$search = $request['search'];
$members = DB::table('users')
          ->select('users.id','users.name','users.lname','users.email','users.phone','users.address_1','users.address_2','users.city','users.postcode','users.unit','users.photo','users.created_at','countries.name as country')
          ->join('countries','users.country','=','countries.id')
          ->where(function($query) {
            $query->where('users.name', 'like' , '%'. $search .'%')
              ->orWhere('users.lname', 'like' , '%'. $search .'%')
              ->orWhere('users.email', 'like' , '%'. $search .'%');
            })
          ->where('users.is_active', '!=', 2)
          ->get();

I tried this and error Undefined variable: search in MemberController.php (line 71)

Last updated 7 years ago.

0

desh901

replied 7 years ago

Try doing this

$search = $request->get('search')

0

saiful.asia

replied 7 years ago

same error. :-(

0

desh901

replied 7 years ago

BTW first of all try to use a dummy value first to check if the query works:

$search = 'teststring';
// Run the query

The solution to your problem is:

->where(function($query) use($search) {
            $query->where('users.name', 'like' , '%'. $search .'%')
              ->orWhere('users.lname', 'like' , '%'. $search .'%')
              ->orWhere('users.email', 'like' , '%'. $search .'%');
            })

Just add the use($search) syntax to your function inside the nested where clause.

Last updated 7 years ago.

0

saiful.asia

replied 7 years ago

hi,

//$search = $request->get('search');
$search = "asd";

it showing error but if i just pass text like

->where('users.name', 'like' , '%asd%')
->orWhere('users.lname', 'like' , '%asd%')
->orWhere('users.email', 'like' , '%asd%')

no error and result is correct It seems query function not get the $search value

0

desh901

replied 7 years ago

Solution

So if the query is correct could you please mark the answer as correct on StackOverflow?

Regarding the $search issue, correct the query as i showed you before:


$search = $request->get('search');
$members = DB::table('users')
          ->select('users.id','users.name','users.lname','users.email','users.phone','users.address_1','users.address_2','users.city','users.postcode','users.unit','users.photo','users.created_at','countries.name as country')
          ->join('countries','users.country','=','countries.id')
          ->where(function($query) use($search){
            $query->where('users.name', 'like' , '%'. $search .'%')
              ->orWhere('users.lname', 'like' , '%'. $search .'%')
              ->orWhere('users.email', 'like' , '%'. $search .'%');
            })
          ->where('users.is_active', '!=', 2)
          ->get();

Just add that use($search) next to your function signature. This happens because when declaring a closure (e.g. a nested function) you are changing the scope of the code inside it, so you need to provide the variables to use inside using the syntax use

Last updated 7 years ago.

0

saiful.asia

replied 7 years ago

Thanks Valerio Cervo :-)

Last updated 7 years ago.

0

desh901

replied 7 years ago

Happy to help ;)

0

desh901