0

How can I dynamically generate query to the db if I don't know how much filter and update - values will be in the $filters and $updates arrays? Template of the query is below:

$model::where($key_1, '=', $filter1)
    ->where($key_2, '=', $filter_2)
    ->where($key_n, '=', $filter_n)
    ->update([$key_1 => $update_1,
        $key_2 => $update_2,
        $key_n => $update_n
    ]);
Karl Hill
  • 12,937
  • 5
  • 58
  • 95
Jurchello
  • 33
  • 1
  • 4
  • Take a look here: https://stackoverflow.com/a/35968797/1779433 – Cameron Hurd Nov 16 '17 at 21:41
  • Possible duplicate of [How to create multiple where clause query using Laravel Eloquent?](https://stackoverflow.com/questions/19325312/how-to-create-multiple-where-clause-query-using-laravel-eloquent) – Maraboc Nov 16 '17 at 22:14
  • You could use a `foreach` loop. Alternatively, depending on their structure, `where` can accept an array of arguments. Post your filter and update arrays. – fubar Nov 16 '17 at 22:26

3 Answers3

0

loop through your arrays and call each method per filter:

foreach($filters as $key =>$value){
   $model->where($key, $value);
}

and then build your $updates array in the same way and call update:

$model->update($updates);
Steph
  • 149
  • 1
  • 2
  • 14
0

I use and recommend query scopes. It keeps your code clean and readable while you can reuse code also.

In my model User I would create a function called scopeFilter

public function scopeFilter($query, $params)
{
    if(isset($params['status']) && trim($params['status']) !== '')
    {
        $query->where('status', '=', trim($params['status']));
    }

    if(isset($params['is_admin']) && is_bool($params['is_admin']))
    {
        $query->where('is_admin', '=', $params['is_admin'] ? '1' : '0');
    }
    return $query;
}

In my ApiUsersController I would just use this call, note that word scope is omitted when making the call.

public function index(Request $request)
{
    $users = User::filter($request->all())->get();

    return Response::make([
        'success' => true,
        'data'    => $users
        ]);
}

Now in your case, you can have a loop in scopeFilter something like following

foreach ($params as $key->$param) {
    $query->where($key, '=', $param);
}

This is to give you an idea how I do it and it keeps the code clean.

Also please read Local Scopes in Laravel documentation here. IMHO it is cleanest way to achieve filtering.

Sallu
  • 479
  • 6
  • 17
0

You can use parameter grouping:

$model::where(function ($query) use ($filters) {
            foreach($filters as $filter) {
                $query->where($filter->column, '>', $filter->value);
            }
            return $query;
        })

...

George Bent
  • 1
  • 1