How to create query like this ?
SELECT id,name,if(id > 17,'yes','no')as present FROM `ref`;
Add a comment
|
2 Answers
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();
Comments
This can be done with a CASE statement
SELECT CASE WHEN <test> THEN <returnvalue>
WHEN <othertest> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
In Laravel use a Raw() SQL statement there is no equivalent in Eloquent.
