Laravel Eloquent Select CASE?

Question

Is there anyone with experience in PHP & Laravel Eloquent who can help me resolve this statement? I'm trying to inject a CASE... WHEN.. END... inside a raw() method. It seemed like it was completely ignored. The existing documentation hasn't been . I've tried several different things to no prevail. I'm trying to pull this off:

SELECT shares.id, ...,     CASE WHEN users.id = <CurrentUser> THEN 1 ELSE 0 END AS is_user,     ... FROM <table> ... 

The source code is below:

$shares = Share::where('shares.status', '=', SHARE_STATUS_APPROVED)                     ->where('shares.deleted', '=', '0')                     ->where('locations.lat', '<=', $nelat)                     ->where('locations.lat', '>=', $swlat)                     ->where('locations.lng', '>=', $nelng)                     ->where('locations.lng', '<=',  $swlng)                     ->where('users.id', '=',  $user)                     ->orWhere('shares.connected_user_id', '=',  $user)                     ->join('users', 'shares.user_id', '=', 'users.id')                     ->join('locations', 'locations.id', '=', 'users.location_id')                     ->join('provinces', 'provinces.id', '=', 'locations.province_id')                     ->join('countries', 'countries.id', '=', 'locations.country_id')                     ->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')                     ->raw('(CASE WHEN users.id = ' . $user . ' THEN 1 ELSE 0 END) AS is_user')                     ->orderBy('shares.created_at', 'desc')                     ->orderBy('users.id', 'asc')                     ->orderBy('shares.connected_user_id', 'asc')                     ->get(); 

Answer 1

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

Answer 2

Alternatively you can use selectRaw instead.

->selectRaw("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,       (CASE WHEN users.id = {$user} THEN 1 ELSE 0 END) AS is_user)")     ->orderBy('shares.created_at', 'desc')