Laravel - passing array of columns in 'where' clause

Question

I have a search query like this:

$data = User::where('first_name', 'like', '%'.$query.'%')
   ->orWhere('last_name', 'like', '%'.$query.'%')
   ->get();

Now, I have many models, each with different column names. Instead of defining a search() function into every controller, I want to do this:

// User
public static function searchableFields()
{
    return ['first_name', 'last_name'];
}

// Some other model
public static function searchableFields()
{
    return ['name', 'description'];
}

And put the search logic in a shared controller, something like this:

$data = $classname::
    where($classname::searchableFields(), 'like', '%'.$query.'%')
    ->get();

How can I achieve this?

Thanks a lot.

Typically column names can't be parameters in the query, not without dynamic SQL. Why is listing out a few columns a problem for you? — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 13, 2017 at 8:14

Jerodev · Accepted Answer · 2017-12-13 09:21:19Z

4

You can loop over the fields and add them to your Eloquent query one by one.

$data = $classname::where(function ($query) use ($classname) {
    foreach ($classname::searchableFields() as $field)
        $query->orWhere($field, 'like', '%' . $query . '%');
})->get();

edited Dec 13, 2017 at 9:21

answered Dec 13, 2017 at 8:28

Jerodev

33.3k11 gold badges96 silver badges113 bronze badges

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

6 Comments

Oscar Over a year ago

Thank you Jerodev! That's exactly what I was looking for. It works like a charm!

Jerodev Over a year ago

Glad I could help. Consider marking this as the solution to your question so other users might also find this.

Marcin Nabiałek Over a year ago

@Oscar be aware this solution might get you into trouble in case you use soft deletes or apply some global scopes.

Oscar Over a year ago

Hi @MarcinNabiałek, I see I can use your solution for soft deletes or some other conditions, like $data = $query->withTrashed()->get();

Marcin Nabiałek Over a year ago

@Oscar Yes, you can. The most important thing is that whenever you use orWhere you should almost always wrap them in where closure as I showed in my answer. Otherwise sooner or later you can get invalid data from database and it might be hard to detect the problem.

|

Marcin Nabiałek · Accepted Answer · 2017-12-13 08:34:54Z

1

I would use scope for that.

You can create base model that all the models should extend (and this model should extend Eloquent model) and in this model you should add such method:

public function scopeMatchingSearch($query, $string)
{
   $query->where(function($q) use ($string) {
       foreach (static::searchableFields() as $field) {
          $q->orWhere($field, 'LIKE',  '%'.$string.'%');
       }
   });
}

Now you can make a search like this:

$data = User::matchingSearch($query)->get();

Just to avoid confusion - $query parameter passed to matchingSearch becomes $string parameter in this method.

answered Dec 13, 2017 at 8:34

Marcin Nabiałek

112k43 gold badges271 silver badges299 bronze badges

Comments

Jordan Lipana · Accepted Answer · 2017-12-13 08:20:00Z

0

You can try something like this.

// Controller

function getIndex(Request $request)
{
    $this->data['users'] = User::orderBy('first_name','asc')->get();

    if ($request->has('keyword')) {
        $results             = User::search($request->keyword);
        $this->data['users'] = collect([$results])->collapse()->sortBy('first_name');
    }
}

// Model

function search($keyword)
{
    $results     = [];
    $search_list =  ['first_name','last_name'];
    foreach ($search_list as $value)
    {
        $search_data = User::where($value,'LIKE','%'.$keyword.'%')->get();
        foreach ($search_data as $search_value) {
            $exist = 0;
            if (count($results)) {
                foreach ($results as $v) {
                    if ($search_value->id == $v->id) {
                        $exist++;
                    }
                }
                if ($exist == 0) {
                    $results[] = $search_value;
                }
            } else{
                $results[] = $search_value;
            }
        }
    }
    return $results;
}

answered Dec 13, 2017 at 8:20

Jordan Lipana

4472 gold badges4 silver badges18 bronze badges

1 Comment

Jerodev Over a year ago

But this will execute as many queries as there are keywords and it has a lot of unnecessary code.

Collectives™ on Stack Overflow

Laravel - passing array of columns in 'where' clause

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest