Laravel query with "if" conditions?

Question

I'm trying to make an advanced search form with Laravel 4, and this is the query:

$result = DB::table('users_ads')
        ->join('ads', 'users_ads.ad_id', '=', 'ads.id')         
        ->orderBy($column, $method)
        ->where('status', TRUE)
        ->where(function($query) use ($input)
        {
            $query->where('short_description', $input['search'])
                    ->where('category', $input['category'])
                    ->where('product', $input['product']);

        })
        ->join('users', 'users_ads.user_id', '=', 'users.id')
        ->select('ads.id', 'ads.img1', 'ads.short_description', 'ads.category', 'ads.product', 'ads.price', 'users.city')
        ->get();

    return $result;

The problem is that the user might not use all the input fields. So i want to include some if conditions in this part:

$query->where('short_description', $input['search'])
                    ->where('category', $input['category'])
                    ->where('product', $input['product']);

.. so if the input is empty, to remove the "where" condition.

Joe · Accepted Answer · 2014-02-21 16:00:22Z

22

You could wrap each where in an if statement.

$query = DB::table('user_ads')
            ->join('ads', 'users_ads.ad_id', '=', 'ads.id')
            ->orderBy($column, $method);

if ($input['search']) {
    $query->where('short_description', $input['search']);
}

if ($input['category']) {
    $query->where('category', $input['category']);
}

$query->join('users', 'users_ads.user_id', '=', 'users.id')
    ->select('ads.id', 'ads.img1', 'ads.short_description', 'ads.category', 'ads.product', 'ads.price', 'users.city')

$result= $query->get();

return $result;

Something along those lines would work I believe.

edited Feb 21, 2014 at 16:00

answered Feb 21, 2014 at 15:55

Joe

1,39410 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Joseph Silber Over a year ago

Not that it makes much of a difference, but you've got the terminology flipped. Before the get call it's a query, afterwards it's a result.

Joe Over a year ago

Oh ya, haha! I was just using the variable provided by the op, didnt really think about that. I shall amend it!

Joseph Silber Over a year ago

The OP chained everything till the get call, so it truly is a result.

Joe Over a year ago

Ya, true. I didnt look at the variable names that much. I prefer your answer either way anyway. A much simpler and cleaner method I believe.

Joe Over a year ago

If you are getting a plain white screen, then that can be good. Have you told the application to output a view or anything? If there is no output, then all you will see is a white screen.

|

Joseph Silber · Accepted Answer · 2017-03-30 14:52:57Z

13

$filters = [
    'short_description' => 'search',
    'category' => 'category',
    'product' => 'product',
];

.....

->where(function($query) use ($input, $filters)
{
    foreach ( $filters as $column => $key )
    {
        $value = array_get($input, $key);

        if ( ! is_null($value)) $query->where($column, $value);
    }
});

Newer version of Laravel have a when method that makes this much easier:

->where(function ($query) use ($input, $filters) {
    foreach ($filters as $column => $key) {
        $query->when(array_get($input, $key), function ($query, $value) use ($column) {
            $query->where($column, $value);
        });
    }
});

edited Mar 30, 2017 at 14:52

answered Feb 21, 2014 at 15:56

Joseph Silber

221k59 gold badges369 silver badges293 bronze badges

1 Comment

Joe Over a year ago

+1 For this, would work well, and better than my answer.

Syscall · Accepted Answer · 2022-10-25 15:06:54Z

0

$role = $request->input('role');
 
$users = DB::table('users')
                
->when($role, function ($query) use ($role) {
                    
          return $query->where('role_id', $role);
                
})
->get();

edited Oct 25, 2022 at 15:06

Syscall

19.8k10 gold badges44 silver badges60 bronze badges

answered Oct 25, 2022 at 14:18

Sam Prasanna

1411 silver badge3 bronze badges

Collectives™ on Stack Overflow

Laravel query with "if" conditions?

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related