DB query with IF condition

1

Hi,
How can I embed an If else condition inside a db query. If the test value is true then it must ignore the where clause.

DB::table(‘products’)
->join(‘brand’, ‘products.brandid’, ‘=’, ‘brand.id’)
->join(‘menu’, ‘products.menuid’, ‘=’, ‘menu.menukey’)
->select(
‘products.id AS prodid’,
‘products.menuid’,
)
->whereRaw(…// this must be ignored if test is true)
->orderBy(‘prodid’, ‘asc’)
->limit(1000)
->get();

2

Hi, I take it you are using eloquent?

Try

->when($gender, function($query) use ($gender) {
        $query->where('gender', '=', $gender);
      })
->get();

Laravel Docs

3

Yes im using eloquent and slim

Sorry can I rephrase, need to test for condition outside of the query using IF. If the test is true, then only include the where in the query.( I would like to include exclude where conditions in the query according to the test)

Hope this make sense.

4

Just include it in the use section of the callback to bring it into scope

$test = true;

->when($gender, function($query) use ($test) {
        (test)? $query->where('gender', '=', $gender) : null
      })
->get();

Im sure that should work

If when is not needed where also allows for a callback

->where(function ($query) use ($test) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();
5

Thank you will try…

6

Just FYI for debugging you can chain the ->dd() / ->dump() methods instead of the ->get() so see the sql ouptut.

1 Like
7

thanks for this tip…

8

Hi,
I missing something, pls help

->when($menuid, function($query) use ($test)
{(test)? $query->where(‘menuid’, ‘=’, $menuid) : null })

 $test = true; // If test is true then do where condition, else ignore
$menuid = 10;

             $productall = DB::table('products')
                    ->join('brand', 'products.brandid', '=', 'brand.id')
                    ->join('menu', 'products.menuid', '=', 'menu.menukey')
                    ->join('supproducts', 'products.id', '=', 'supproducts.id')
                    ->select(
                            'products.id AS prodid',
                            'products.menuid',
                         
                    )
                    ->offset(0)
                    ->when($menuid, function($query) use ($test)
                    {(test)? $query->where('menuid', '=', $menuid) : null })
                    ->orderBy('menuid', 'asc')
                    ->orderBy('prodid', 'asc')
                    ->limit(1000)
                    ->get();
9

You need to pass menuid to the callback function and the function has to return something:

->when($menuid, function($query) use ($test, $menuid) {
    return $test ? $query->where('menuid', '=', $menuid) : null;
})

Also you can clean this up a bit more:

->when($menuid && $test, function($query) use ($menuid) {
    return $query->where('menuid', $menuid);
})
1 Like
10

Hi, Thanks for responding, its Working…how can I add and ElseIF to this

->when($menuid && $test, function($query) use ($menuid) {
    return $query->where('menuid', $menuid);
})
11

Depends what you’re trying to do. Either change the callback function or add another when method.

Show us an example of the required conditional statement and we can help you more.

12

thanks, that will also work, sometime the simplest solution works the best

13

Hi,

Can you please help.
In the current ->when clause, could I use and IF / Else condition on the $_stockcode

Filter conditions
$_stockcode
=1 (If stock value greater than 0)
= 2 (if stock = 0)

 ->when($_menuid > 0 && $_brandid == 0 &&  $_stockcode , function($query) use ($_menuid) {
                    return $query
                            ->where([
                                ['menuid', '=', $_menuid],
                                ['qty', '>', 0],
                            ])
                            ->orderBy('prodid', 'asc');
                })
14

You can create another when method next to the previous one or within the callback function, or you can return based on conditions you have.

As you’re using Laravel’s query builder, I would suggest to read through their docs too, everything is explained there pretty well: https://laravel.com/docs/7.x/queries

15

thanks you, really appreciated