Convert mysql query logic to Laravel query builder

Question

I am trying to convert my mysql query logic to Laravel query builder. I I have no Idea how to convert it as laravel query.

my query logic is

SELECT id,name,
case 
    when visibility_status = '1' 
    then 'Visible' 
    when visibility_status = '0' 
    then 'Invisible'
    end as visibility_status FROM `flowers`

generally I write a select query using query builder but cant implement above logic

$result = DB::table('flowers')
        ->select('flowers.id as id', 'flowers.name as name',
'flowers.visibility_status as visibility_status');

A different approach might be to add a table to decode the value and then join to it. So you'd have a small table that just had two rows and two columns: 0|Invisible, 1|Visible. Then in either plain SQL or in (non-raw-query) Laravel Query Builder you'd just join from flowers to the decode table and read the value from there. Alternatively, just read the 0 and the 1 and decode them directly in the Laravel View where you output them, assuming that's what you're doing with them. — Matt Gibson
– Matt Gibson, Commented May 2, 2015 at 9:46

Satish Shinde · Accepted Answer · 2015-05-02 09:39:25Z

7

Try This

$users = DB::table('flowers')
->select(["id", "name",
      DB::raw("
       case 
          when visibility_status = '1' 
          then 'Visible' 
          when visibility_status = '0' 
          then 'Invisible'
          end as visibility_status
    ")])->get();

Here is the reference for it http://laravel.com/docs/4.2/queries#raw-expressions

answered May 2, 2015 at 9:39

Satish Shinde

3,0061 gold badge26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MackieeE Over a year ago

To note: You'd need PHP 5.4 >= to make this work I believe?

lukasgeiter Over a year ago

@MackieeE PHP >= 5.4 is a requirement for Laravel anyways

Collectives™ on Stack Overflow

Convert mysql query logic to Laravel query builder

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related