Laravel Eloquent query with optional parameters

Question

I am trying to learn whether or not there is a simple way to pass a variable number of parameters to a query in Eloquent, hopefully using an array.

From what I can find, there doesn't seem to be a way to do this without looping through the Input to see what was set in the request.

Examples here: Laravel Eloquent search two optional fields

This would work, but feels non-Laravel to me in its complexity/inelegance.

Here is where I am, and this may not be possible, just hoping someone else has solved a similar issue:

$where = array("user_id" => 123, "status" => 0, "something else" => "some value");

        $orders = Order::where($where)->get()->toArray();
        return Response::json(array(
                'orders' => $orders
                ),
            200
        );

That returns an error of course strtolower() expects parameter 1 to be string, array given.

Is this possible?

Answer 1

Order::where actually returns an instance of query builder, so this is probably easier than you thought. If you just want to grab that instance of query builder and "build" your query one where() at a time you can get it like this:

$qb = (new Order)->newQuery();
foreach ($searchParams as $k => $v) {
    $qb->where($k, $v);
}
return $qb->get(); // <-- fetch your results

If you ever want to see what query builder is doing you can also execute that get() and shortly after:

dd(\DB::getQueryLog());

That will show you what the resulting query looks like; this can be very useful when playing with Eloquent.

Answer 2

You can try this:

Method 1:

If you have one optional search parameter received in input

$orders = Order::select('order_id','order_value',...other columns);

if($request->has(user_id)) {
   $orders->where('orders.user_id','=',$request->user_id);
}

//considering something_else as a substring that needs to be searched in orders table
if($request->has('something_else')) {
    $orders->where('orders.column_name', 'LIKE', '%'.$request->something_else.'%');
}

$orders->paginate(10);

Method 2:

If you have multiple optional parameters in input

$orders = Order::select('columns');
foreach($input_parameters as $key => $value) {
    //this will return results for column_name=value
    $orders->where($key, $value);//key should be same as the column_name

    //if you need to make some comparison
    $orders->where($key, '>=', $value);//key should be same as the column_name
}
return $orders->paginate(15);