searching... searching... - 7 months ago 76
PHP Question

How to create conditional query with IF(expression, value_if_true,value_if_false) in laravel?

How to create query like this ?

SELECT id,name,if(id > 17,'yes','no')as present FROM `ref`;

Answer

There are several ways to achieve this in Laravel. You can choose to go with the Query builder route or The Eloquent route

With Query builder it will go like this

DB::table('ref')->select(['id','name',DB::raw('(case when (id > 17) then "yes" else "no" end) as present')])->get();

And with Eloquent you can choose to use Accessor and Mutators or add DB::raw in your select query

with Mutators and Accessors, You first append your new header/attribute to your attributes like so

protected $appends = ['present'];

then you write your condition for your new header/attribute.

public function getPresentAttribute(){

   if($this->attributes['id'] > 17)
      return $this->attributes['present'] = "yes";
   return $this->attributes['present'] = "no"; 

}

With this anytime you query the Ref Model the present attribute will be added to your query result with a value of yes or no depending on your condition.

And you can choose to use the DB::raw in your Eloquent also thereby to forgo the accessor/mutator route

App\Ref::select(['id','name',DB::raw('(case when (id > 17) then "yes" else "no" end) as present')])->get();