Laravel: How to search DB for a given string using eloquent?

Question

I would like to retrieve data matching the search string, provided, it must not belongs to current user.

if($str_search && $str_search != '' )
{
  $mandate_list = Mandate::select(array('umrn','mandate_status', 'payment_type','currency','fixed_amount','max_amount','user_id','id','debtor_bank','creditor_bank','refusal_reason'))
                        ->orWhere('id', 'LIKE', '%'.$str_search.'%')
                        ->orWhere('umrn','LIKE','%'.$str_search.'%')
                        ->orWhere('mandate_status','LIKE','%'.$str_search.'%')
                        ->orWhere('mandate_date','LIKE','%'.$str_search.'%')
                        ->orWhere('payment_type','LIKE','%'.$str_search.'%')
                        ->orWhere('currency','LIKE','%'.$str_search.'%')
                        ->orWhere('fixed_amount','LIKE','%'.$str_search.'%')
                        ->orWhere('mandate_date','LIKE','%'.$str_search.'%')   
                        ->where('user_id', '<>', $current_user) 
                        ->orderBy('updated_at', 'desc')                            
                        ->get();

}

But this query displays all records (even belongs to current user). How can i solve this. Thanks for your suggestions.

Joseph Silber · Accepted Answer · 2015-04-14 14:25:26Z

3

Group all your orWheres in a closure:

Mandate::select([.....])->where(function($query) use ($str_search)
{
    $columns = ['id', 'umrn', 'mandate_status', ...];

    foreach ($columns as $column)
    {
        $query->orWhere($column, 'LIKE', '%'.$str_search.'%');
    }
})
->where('user_id', '<>', $current_user) 
->orderBy('updated_at', 'desc')                            
->get();

edited Apr 14, 2015 at 14:25

answered Apr 13, 2015 at 16:09

Joseph Silber

221k59 gold badges369 silver badges292 bronze badges

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

3 Comments

Nithil George Over a year ago

When I implement this code I am getting {"error":{"type":"ErrorException","message":"Undefined variable: str_search","file" in foreach .

Merhawi Fissehaye Over a year ago

You can pass the $str_search variable to the function callback using use: [....]->where(function($query) use ($str_search) {[.....]});

Joseph Silber Over a year ago

@NithilGeorge - updated the answer to use your $str_search variable.

Collectives™ on Stack Overflow

Laravel: How to search DB for a given string using eloquent?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related