Monday, March 6, 2017

Dynamically generating closures based in input for search (Laravel / Elequent)

I'd like to generate an query like this:

select * from `recipes` where ((`menu` LIKE '%dinner%' or `menu` LIKE '%lunch%') and (`type` LIKE '%steak%' or `soort` LIKE '%chicken%')) order by RAND() asc limit 1

While my input looks like this:

"menu1" => "dinner" "menu2" => "lunch" "type1" => "steak" "type2" => "chicken"

This is how my code looks right now:

  foreach ($input as $key => $value) {
            $key = preg_replace('/[0-9]+/', '', $key);
            if($oldKey == $key){
                $andOr = 'OR';
            } else {
                $andOr = 'AND';
            }

                if($oldKey == $key)
                {
                    $query->where(function($query) use ($key, $value){
                       $query->where($key, "LIKE", "%$value%");
                    });
                } else {
                    $query->orWhere(function($query) use ($key, $value)
                    {
                        $query->orWhere($key, "LIKE", "%$value%");
                    });
                }

            }

            $oldKey = $key;
    });

    $recipe = $query->orderBy(DB::raw('RAND()'))->limit(1)->get();

but sadly this generating something like this:

select * from `recipes` where ((`menu` LIKE '%dinner%') or (`menu` LIKE '%lunch%') or (`type` LIKE '%steak%') or (`type` LIKE '%chicken%')) order by RAND() asc limit 1 

Which isn't correct.

I'm trying to make this piece of code dynamic so i can give it more or less input variables and still generates a "valid" query. With valid i mean the parentheses are placed like in the first query.

I hope i made everything clear and thanks for any help!



via Ivarkentje

Advertisement