Laravel 5 Database Like query on a array

Question

I have been working on a laravel advance search filter where a user can input several field in a form and on submit it generates data table for given fields. and if fields are empty then it just shows all the records from database (unsets the filter).

 public function advanced_search(Request $request)
{

    //user submitted data in post
    $post = $request->all();

  /*

    filter array keys are defined here
    e.g first_name, last_name

  */

    $simple_filter = array(
        "first_name" => "",
        "last_name" => "",
        "email" => "",
        "company_name" => "",
    );
    foreach ($simple_filter as $key => $value) {
        if (isset($post[$key]) && ($post[$key] != null) && !empty($post[$key])) {
            $simple_filter[$key] = $post[$key];
        } else {
            //user didn't send this field in post data so removing this from filter list

            unset($simple_filter[$key]);
        }
    }

    $query = DB::table('contacts')->where($simple_filter)->get();
    return DataTables::of($query)
        ->toJson();
}

Now this code is working fine. But requirements are changed. This code only return the exact details. Now I want to show record even if substring matches. (LIKE %data$). How can I modify this?

Alexey Mezenin · Accepted Answer · 2018-01-08 09:54:20Z

2

Use orWhere() if you want to find contacts where one of the fields is like a given form input. Or use where() if you want to use all the form input elements to filter contacts:

public function advanced_search(Request $request)
{
    $query = Contact::query();
    $fields = ['first_name', 'last_name', 'email', 'company_name'];
    foreach ($fields as $field) {
        if ($request->filled($field)) {
            $query = $query->orWhere($field, 'like', '%' . $request->get($field) . '%');
        }
    }
    return DataTables::of($query->get())->toJson();
}

Also, you're doing a lot of redundant checks. Just use the ->filled() method instead. It will return false if a field is empty, or doesn't exist or null.

If you would like to determine if a value is present on the request and is not empty, you may use the filled method

https://laravel.com/docs/5.5/requests#retrieving-input

edited Jan 8, 2018 at 9:54

answered Jan 8, 2018 at 9:40

Alexey Mezenin

164k27 gold badges309 silver badges287 bronze badges

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

2 Comments

Amarnasan Over a year ago

This is ok, but maybe he only wants entries that match all of the filled fields, and not any that matches any filled field.

AHSAN Over a year ago

This is nice. And working. I'll go with this one. Thanks for replying here. Much appreciated.

Jerodev · Accepted Answer · 2018-01-08 10:40:40Z

2

You can create the query before the loop and add all where filters in the loop. The second parameter of the where function can be the comparer, in your case: like.

$query = DB::table('contacts');

foreach ($simple_filter as $key => $value) {
    if (isset($post[$key]) && ($post[$key] != null) && !empty($post[$key])) 
    {
        $query = $query->orWhere($key, 'like', '%' . $value . '%');
    }
}

return DataTables::of($query->get())->toJson();

edited Jan 8, 2018 at 10:40

answered Jan 8, 2018 at 9:35

Jerodev

33.3k11 gold badges96 silver badges113 bronze badges

1 Comment

AHSAN Over a year ago

I tried this but its not working properly. Its giving me all the records every time. But I get the idea.. I'll try to make it work somehow. Thanks for replying here

Collectives™ on Stack Overflow

Laravel 5 Database Like query on a array

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related