Laravel Eloquent Select CASE?
90,359
Solution 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
Solution 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')
Comments
-
Coach Roebuck over 3 years
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();
-
Arun P almost 5 yearsNice and very useful
-
Thanura Jayasekara about 3 yearsIf you are using array to select the fields push the raw query to that array and use it example: array_push($columnArr, DB::raw("(CASE WHEN isActive = 1 THEN 'Active' ELSE 'Inactive' END) as Active_Status")); And then write DB::table($table) ->select($columnArr)
-
Kamlesh almost 3 yearsHow can we set a where condition with CASE statements? Kindly suggest. Thanks
-
shaedrich almost 3 years
'(CASE WHEN users.id = ' . $user . ' THEN 1 ELSE 0 END) AS is_user'
looks like you could do an SQL injection ...