CODEWITHSUNDEEP
phplaravellaravel-5
Script Lover
Script Lover

Reputation: 381

Laravel search query does not working using elquent

I have query like below, which fetch the records. 

$posts = User::whereHas('roles', function($q)use($search){
                    $q->where('name', '=', 'agent')
                    ->where('first_name', 'like', "%".$search."%")
                    ->orWhere('last_name','like',"%".$search."%")
                    ->orWhere('created_at','like',"%".$search."%");
                })->limit($limit)
                ->orderBy($order,$dir)
            ->get();

I the above query I am using for pagination means to get next 10 records, but it does not working.

Following variables are posting from ajax 

$limit = $request->input('length');
$start = $request->input('start');
$order = $columns[$request->input('order.0.column')];
$dir = $request->input('order.0.dir');

Ajax is working fine but I always get first 10 records when I submit for next 10 records. Can someone kindly help. Thank you

Upvotes: 0

Views: 142

Answers (2)

Bhuvanesh Soni
Bhuvanesh Soni

Reputation: 206

For searching with multiple column with single key you can do like this:

$employee = Employee::whereHas('roles', function ($query) {
                $query->where('name', '=', 'developer');
            })
            ->where(function ($q) use ($search) {
                if ($search != '') {
                    $query->where('employee_name', 'LIKE', '%' . $search . '%');
                    $query->orWhere('email', 'LIKE', '%' . $search . '%');
                    $query->orWhere('phone', 'LIKE', '%' . $search . '%');
                }
                $query->Where('type', '2');
            })
            ->offset($start)
            ->limit($limit)
            ->orderBy('employee_name')
            ->get();

Upvotes: 3

aynber
aynber

Reputation: 23011

You have two issues: You never pass in the offset, so you'll always get the same 10 records. And you mix your AND/ORs, so that it won't limit the name to agent. So to fix it, pass the ORs in another enclosure:

$posts = User::whereHas('roles', function($q)use($search){
    $q->where('name', '=', 'agent')
        ->where(function($query) use ($search) {
            $query->where('first_name', 'like', "%".$search."%")
                ->orWhere('last_name','like',"%".$search."%")
                ->orWhere('created_at','like',"%".$search."%");
        });

})
    ->offset($start)
    ->limit($limit)
    ->orderBy($order,$dir)
    ->get();

Upvotes: 2

Related Questions