Laravel is an Eloquent extension where suggestions based on dynamic parameters
I would like to build a series of eloquent WHERE clauses depending on the search parameters that I am gathering from a json object.
Something like this (not to mention the syntax of the object, this is an interpretation for demonstration only):
$searchmap = "
{
"color": "red",
"height": "1",
"width": "2",
"weight": "",
"size": "",
}";
Then I take the object and decode to get the search array ...
$search = json_decode($searchmap, true);
If my weight and size are zero or an "empty string" I would have eloquent code that looks like this.
$gadgets = Gadget::where('color', '=', $search['color'])
->where('height', '=', $search['height'])
->where('width', '=', $search['width'])
->paginate(9);
If they have a meaning, then the eloquent code would look like this.
$gadgets = Gadget::where('color', '=', $search['color'])
->where('height', '=', $search['height'])
->where('width', '=', $search['width'])
->where('weight', '=', $search['weight'])
->where('size', '=', $search['size'])
->paginate(9);
Is there a way to accomplish this dynamically.
I suppose there should be a way to chain eloquence in this question, where the clauses are dynamically based on a given parameter?
In a pseudo context, I want to do something like this
$gadgets = Gadget::
foreach ($search as $key => $parameter) {
if ( $parameter <> '' ) {
->where($key, '=', $parameter)
}
}
->paginate(9);
Could the chain of creation be something similar somewhere?
Thanks for taking the time to look at this!
UPDATE:
I also came up with something like this, which seems to work well, but I'd like to welcome suggestions if improving is a good idea.
$gadgets = New Gadget();
foreach ($search as $key => $parameter) {
if($parameter != ''){
$gadgets = $gadgets->where($key, '=', $parameter);
}
}
$gadgets = $gadgets->paginate(9);
FINAL
And thanks to @lukasgeiter below I think I will go with this
$gadgets = Gadget::whereNested(function($query) use ($search) {
foreach ($search as $key => $value)
{
if($value != ''){
$query->where($key, '=', $value);
}
}
}, 'and');
$gadgets = $gadgets->paginate(9);
source to share
It's easy. Laravel function where
allows you to pass an array of key value pairs.
$searchmap = array(
'color' => 'red',
'height' => '1'
// etc
);
$gadgets = Gadget::where($searchmap)->paginate(9);
If you are wondering what the relevant part of the source ( \Illuminate\Database\Query\Builder
)
public function where($column, $operator = null, $value = null, $boolean = 'and')
{
// If the column is an array, we will assume it is an array of key-value pairs
// and can add them each as a where clause. We will maintain the boolean we
// received when the method was called and pass it into the nested where.
if (is_array($column))
{
return $this->whereNested(function($query) use ($column)
{
foreach ($column as $key => $value)
{
$query->where($key, '=', $value);
}
}, $boolean);
}
// many more lines of code....
}
Edit
To have more control over it (for example changing "=" to another comparison operator) try using laravel code using internally directly:
$gadgets = Gadget::whereNested(function($query) use ($searchmap)
{
foreach ($searchmap as $key => $value)
{
if($value != ''){
$query->where($key, '=', $value);
}
}
}, 'and')->paginate(9);
source to share
For those who need it, here's a modified version of lukasgeiter's answer that solves the "variable number of wheres" problem and also allows (1) different operators for each sentence where
and (2) the ability to also use whereIn
if one of your "wheres "must be able to match one of several values (the function below determines when an array of values is passed and thus uses whereIn
instead where
).
The variable assignment $paramSets
at the beginning (below) essentially describes how to use it.
$paramSets = [
"size"=>["op"=>"=","values"=>"large"],
"production_date"=>["op"=>">=","values"=>"2015-12-31"],
"color"=>["op"=>"=","values"=>["red","blue"]],
"name"=>["op"=>"like","values"=>"M%"]
];
$items = db::table('products')
->whereNested(function($query) use ($paramSets) {
foreach ($paramSets as $k => $v)
{
if ($v != ''){
if (is_array($v["values"])){
$query->whereIn($k,$v["values"]);
}
else {
$query->where($k, $v["op"], $v["values"]);
}
}
}
}, 'and');
dd($items->get());
source to share