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

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:

the expectations I want after the query:

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0