CODEWITHSUNDEEP
phpsqllaravel
Jeton Thaçi
Jeton Thaçi

Reputation: 815

Laravel query with "if" conditions?

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.

Upvotes: 7

Views: 78821

Answers (3)

Sam Prasanna
Sam Prasanna

Reputation: 141

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

Upvotes: 0

Joseph Silber
Joseph Silber

Reputation: 220136

$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);
        });
    }
});

Upvotes: 13

Joe
Joe

Reputation: 1394

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.

Upvotes: 22

Related Questions