Laravel 5.2 - filter with array

Question

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:

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!

Consider writing your questions better. There is just so much there. Like "inputs" and checking if things are empty. Wouldn't it be better if you just said, $filter = [..]; $posts = Post::where($filter)->get(); gave so and so error. What am I doing wrong? You will get more answers that way. — Yahya Uddin
– Yahya Uddin, Commented Oct 29, 2016 at 11:39

Yahya Uddin · Accepted Answer · 2016-10-29 12:00:19Z

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();

Amit Gupta · Accepted Answer · 2016-10-29 11:57:27Z

1

You can chain the where() function in query builder instance as:

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

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

$posts = $query->get();

answered Oct 29, 2016 at 11:57

Amit Gupta

17.7k5 gold badges46 silver badges54 bronze badges

Collectives™ on Stack Overflow

Laravel 5.2 - filter with array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related