duancong7358
2014-10-31 20:22
浏览 90
已采纳

Laravel - 根据动态参数扩展Eloquent子句

I would like to construct a series of eloquent WHERE clauses dependent on the search parameters I collect from a json object.

Something like this (never mind the syntax of object,,, it is an interpretation only to demonstrate):

$searchmap = "
{
    "color": "red",
    "height": "1",
    "width": "2",
    "weight": "",
    "size": "",
}";

I then take the object and decode to get a search array...

$search = json_decode($searchmap, true);

If my weight and size are set to null or are 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 value then 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 the question should be ins there a way to chain eloquent where clauses dynamically based on a given parameter?

In a pseudo context I am looking to do something like this

$gadgets = Gadget::

    foreach ($search as $key => $parameter) {
        if ( $parameter <> '' ) {
            ->where($key, '=', $parameter)
        }
    }

->paginate(9);

Can chaining of where clauses be created in some way similar to this?

Thank you for taking the time to look at this!


UPDATE:

I also came up with something like this that seems to work well but i would like to welcome suggestions if improvement 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);
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dqy27359 2014-10-31 20:28
    已采纳

    That's easy. Laravel's where function allows you to pass in an array of key value pairs.

    $searchmap = array(
        'color' => 'red',
        'height' => '1'
        // etc
    );
    
    $gadgets = Gadget::where($searchmap)->paginate(9);
    

    If you are curious, that's 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 (e.g. changing the "=" to another comparison operator) try using the code laravel uses internally directly:

    $gadgets = Gadget::whereNested(function($query) use ($searchmap)
            {
                foreach ($searchmap as $key => $value)
                {
                    if($value != ''){
                        $query->where($key, '=', $value);
                    }
                }
            }, 'and')->paginate(9);
    
    已采纳该答案
    打赏 评论
  • duanji5116 2016-03-26 04:53

    For anyone who needs it, here's a modified version of lukasgeiter's answer that solves the 'variable number of wheres' problem while also allowing (1) different operators for each where clause and (2) the capacity to also use whereIn for when one of your "wheres" must be able to match one of multiple values (the function below detects when an array of values is passed and, thus, uses whereIn instead of where).

    The $paramSets variable assignment 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());
    
    打赏 评论

相关推荐 更多相似问题