Laravel Eloquent Select CASE?

Question

有没有熟悉PHP和Laravel Eloquent的人能够帮我解决这个语句？我正在尝试在raw()方法中插入CASE... WHEN.. END...。它似乎被完全忽略了。现有的文档并没有解决我的问题。我尝试了几种不同的方法，但都没有成功。我想要实现以下内容：

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

以下是源代码：

$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();

- Coach Roebuck

Answer 1

将raw()函数调用移至SELECT语句内：

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

来源： https://laravel.com/docs/5.4/queries#raw-expressions

- Alexandre Danault

---

不错，非常有用。 - Arun P

如果您正在使用数组选择字段，请将原始查询推送到该数组中并使用它作为示例： array_push($columnArr, DB::raw("(CASE WHEN isActive = 1 THEN 'Active' ELSE 'Inactive' END) as Active_Status")); 然后编写 DB::table($table)->select($columnArr) - Thanura Jayasekara

我们如何在CASE语句中设置where条件？请提供建议。谢谢。 - Kamlesh

'(CASE WHEN users.id = ' . $user . ' THEN 1 ELSE 0 END) AS is_user' 看起来你可能会进行 SQL 注入... - shaedrich

Answer 2

或者您可以使用selectRaw代替。

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

- MM2

Answer 3

最近我创建了一个包，为Eloquent Query Builder添加了CASE支持。您可以在这里查看它：https://github.com/aglipanci/laravel-eloquent-case

使用该包，您可以做到以下操作：

use App\Models\Invoice;
use AgliPanci\LaravelCase\Query\CaseBuilder;

$invoices = Invoice::query()
            ->case(function (CaseBuilder $case) {
                $case->when('balance', '<', 0)->then('Overpaid')
                    ->when('balance', 0)->then('Paid')
                    ->else('Balance Due');
            }, 'payment_status')
            ->get();

- Agli Panci