Laravel search query does not working using elquent

Question

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

You're mixing AND and OR in your subquery. You'll need to add another enclosure to separate out there ORs. You also never pass in an offset. — aynber
– aynber, Commented Jan 9, 2019 at 15:10
@aynber can you please post the answer I would very appreciate. I need help +1 — Script Lover
– Script Lover, Commented Jan 9, 2019 at 15:11
laravel.com/docs/5.7/queries#parameter-grouping See the second where() in that example, how it wraps a where() and orWhere() clause? See if you can implement that. — Tim Lewis
– Tim Lewis, Commented Jan 9, 2019 at 15:14

Bhuvanesh Soni · Accepted Answer · 2019-02-12 07:51:32Z

3

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();

answered Feb 12, 2019 at 7:51

Bhuvanesh Soni

2061 gold badge2 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aynber · Accepted Answer · 2019-01-09 15:16:08Z

2

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();

answered Jan 9, 2019 at 15:16

aynber

23.2k9 gold badges57 silver badges69 bronze badges

3 Comments

Script Lover Over a year ago

When I submit search input with some keyword then it is working fine but when I submit search as blank to get next 10 records then it is always showing first 10 records this is the problem I am facing

Script Lover Over a year ago

Actually if(empty($request->input('search.value'))){ is not checking correctly

aynber Over a year ago

If you want to get the next 10 records, you'll need to either save or pass the search in again, or else it's not going to have the search to look for, and you won't be paging off the same results. If the input is not checking correctly, you may want to dd($request->input()); or Log::info($request->input()); to make sure you get the right values.

Collectives™ on Stack Overflow

Laravel search query does not working using elquent

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related