laravel 7: how to display data with if / case statements in the raw / query builder

Question

I want to add a new field called status to the select query, where the condition of the field is the result of a select query using a case / if statement.

This is my table now:

fullname	is_active
Mr. X	false
Mr. Z	true

the expectations I want after the query:

fullname	is_active	status
Mr. X	false	Yes
Mr. Z	true	No

This is my code, but still displays an error: Undefined column: 7 ERROR: column "0" does not exist LINE 2: WHEN app.is_active = "0" THEN "Yes"

$data = DB::table('my_table as app')
   ->select('fullname','is_active',
             DB::raw('(CASE 
                WHEN app.is_active = "0" THEN "Yes" 
                ELSE "No" 
                END) AS status'))
   ->get();

Thakns a lot.

Answer 1

Try using single quotes for MySQL string literals:

$data = DB::table('my_table as app')
    ->select('fullname', 'is_active',
             DB::raw("CASE app.is_active WHEN '0' THEN 'Yes' ELSE 'No' END AS status"))
    ->get();

If the above fixes your problem, then I would actually be a bit surprised, because MySQL usually will accept double quotes for string literals. The error message you see implies that "0" is being interpreted as a column name instead.