Diego Cespedes Diego Cespedes - 1 month ago 18
PHP Question

Laravel 5.2 - filter with array

i'm doing a search filter, i have 3 inputs "municipality", "category", "keyword", i'm tryng to insert value to array IF input is not empty. like this:

public function search(Request $request)
{


$filter = array(["'visible', '=' , 1"],["'expire_date', '>', $current"]);

if(!empty($termn)){
$filter[] =["'title', 'LIKE' , '%'.$termn.'%'"];
}
if(!empty($request->input('category'))){
$filter[] = ["'category_id', '=', $input_category"];
}
if(!empty($request->input('municipality_id'))) {
$filter[] = ["'municipality_id', '=', $input_municipality"];
}

dd($filter);

$posts = Post::where($filter)->get();
}


But it is not filtering well, the dd($filter) return like this:
enter image description here

maybe the structure of array is not ok, i tried also like this:
laravel 5.2 search query
but it doen't work.
WITHOUT dd($filter) i have this error:


SQLSTATE[42000]: Syntax error or access violation: 1064 You have an
error in your SQL syntax; check the manual that corresponds to your
MariaDB server version for the right syntax to use near '.
is null
and `'municipality_id', '=', 1` is null)' at line 1 (SQL: select *
from `posts` where (`'visible', '=' , 1` is null and `'expire_date',
'>', 2016-10-29 13:29:30` is null and `'category_id', '=', Scegli una
categoria`.
.
.
is null and
'municipality_id', '=', 1
is null))


Thank you for your help!

Answer

You are using the where clause wrong. See the following documentation:

Options for where clauses in models should be sent as parameters in chained methods (NOT array values) like so:

public function search(Request $request)
    {
        $current = Carbon::now();
        $current = new Carbon();
        $termn = $request->input('keyword');
        $input_category = $request->input('category');
        $input_municipality = $request->input('municipality_id');


        $posts = Post::where('visible', 1)->where('expire_date', '>', $current);

        if(!empty($termn)){
                $posts->where('title', 'LIKE' , '%'.$termn.'%');
        }
        if(!empty($request->input('category'))){
                $posts->where('category_id', '=', $input_category);
        }
        if(!empty($request->input('municipality_id')))  {
                $posts->where('municipality_id', '=', $input_municipality);
        }

        $post_results = $posts->get();
        dd($posts_results);
}

Note you can send a query as an array for database tables (not models) like so:

$users = DB::table('posts')->where([
    ['visible', '=', '1'],
    ['expire_date', '>', $current],
    // ...
])->get();