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:

Mr. Xfalse
Mr. Ztrue

the expectations I want after the query:

Mr. XfalseYes
Mr. ZtrueNo

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')
                WHEN app.is_active = "0" THEN "Yes" 
                ELSE "No" 
                END) AS status'))

Thakns a lot.


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"))

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 or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x