Laravel Eloquent Select Case

Laravel Eloquent Select CASE?

Move your raw() call inside the SELECT statement:

->select('shares.id AS share_id', 'users.id AS user_id', 'shares.connected_user_id',    
  'shares.original_language_id', 'shares.image',
  'users.first_name', 'users.last_name', 'users.email',
  'locations.city', 'provinces.name', 'countries.code',
  'locations.lat', 'locations.lng',
  'shares.created_at',
  DB::raw('(CASE WHEN users.id = ' . $user . ' THEN 1 ELSE 0 END) AS is_user')
  )
->orderBy('shares.created_at', 'desc')

From: https://laravel.com/docs/5.4/queries#raw-expressions

Laravel - How to add a JOIN and CASE-WHEN using Query Builder or Eloquent?

You can try with below query

myFriendsData = DB::table('audioassets as aa')
    ->where('ks.keysig', 'LIKE', '%')
    ->join('instruments', 'audioassets.instrument_id', '=', 'instruments.id')
    ->leftjoin('ragalist', 'audioassets.raga_id', '=', 'ragalist.id')
    ->join('keysigs', 'audioassets.keysig_id', '=', 'keysigs.id')
    ->join('likes ',audioassets.id = likes.audioassets_id, function() {})
    ->whereRaw(
        '( 
            CASE 
                 WHEN likes.user_id = 1 THEN true ELSE false     
            END
        )'
    )
    ->select('audioassets.filepath', 'audioassets.filename','instruments.instrument', 'ragalist.raga', 'keysigs.keysig', 'audioassets.id', 'likes.id as likesid');
    ->get();

apply sql case on select all in laravel

To retrieve all the columns you can use DB::raw method also.

$products = Product::where('is_archive',0)->select(
            DB::raw('products.*'),
            DB::raw('(CASE WHEN pick_8 = 1 THEN "True" ELSE "False" END) AS pick_8'),
            DB::raw('(CASE WHEN pick_12 = 1 THEN "True" ELSE "False" END) AS pick_12'))->get()->toArray();

CASE statement will overwrite original values of pick_8 and pick_12 columns.

P.S. By the way. I guess you trying to use wrong tools to reach your goal. Take a look at eloquent-mutators

Case When Like in Eloquent in Laravel

Try with "(CASE WHEN akun LIKE '%51%' THEN 51 ELSE 0 END )" Notice the double quote to contain the whereRaw statement.

And to send dynamic data you can try "(CASE WHEN akun LIKE '%".data."%' THEN 51 ELSE 0 END )"

So your query should look like this:

$realisasi=Realisasi::whereRaw("(CASE WHEN akun LIKE '%".$data."%' THEN 51 ELSE 0 END )')
                    ->whereYear('tanggal','2020')
                    ->where('kdsatker','412772')
                    ->groupby('akun')
                     ->get();

MySQL requires the like pattern to be inside single quotes. For more reference: https://dev.mysql.com/doc/refman/8.0/en/pattern-matching.html

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

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.

laravel query with case statement in where clause append IS NULL after condition

Replace your DB::raw with whereRaw

->where([
     'er.id_user_request_to' => $nIdUser
   ])
->whereRaw(('case WHEN ge.id_user IS NOT NULL THEN ge.id_user = '.$nIdUser.' ELSE 0 END'))

I hope it help's

---

Related Topics