请求将指定MySQL查询转换为Laravel Eloquent ORM代码

原始MySQL查询 #

SELECT
    team_name,
    (CASE WHEN `assignment` = 'Appeal/Recon/Claim Review' THEN `done` ELSE 0 END) AS 'Appeal', 
    (CASE WHEN `assignment` = 'IDR' THEN `done` ELSE 0 END) AS 'IDR'
FROM
    operation_assignments  
WHERE
    serial = 6 
GROUP BY
    team_name

现有查询构建器实现 #

$collections = DB::table('operation_assignments')
    ->select(
        'team_name', 
        DB::raw("CASE WHEN assignment = 'Appeal/Recon/Claim Review' THEN done ELSE 0 END AS Appeal"),
        DB::raw("CASE WHEN assignment = 'IDR' THEN done ELSE 0 END AS IDR")
    )
    ->where('serial', 6)
    ->groupBy('team_name')
    ->get();

Eloquent ORM实现方案 #

1. 先创建对应模型类 #

确保项目中存在与operation_assignments表对应的Eloquent模型，示例如下：

// app/Models/OperationAssignment.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class OperationAssignment extends Model
{
    // 若表名遵循Laravel蛇形命名规范（模型类名驼峰转蛇形+s），此属性可省略
    protected $table = 'operation_assignments';
    // 根据实际字段调整可批量赋值的字段列表
    protected $fillable = ['team_name', 'assignment', 'done', 'serial'];
}

2. 基于模型执行查询 #

直接通过模型替代DB::table()调用，逻辑与查询构建器完全一致：

use App\Models\OperationAssignment;

$collections = OperationAssignment::select(
        'team_name', 
        \DB::raw("CASE WHEN assignment = 'Appeal/Recon/Claim Review' THEN done ELSE 0 END AS Appeal"),
        \DB::raw("CASE WHEN assignment = 'IDR' THEN done ELSE 0 END AS IDR")
    )
    ->where('serial', 6)
    ->groupBy('team_name')
    ->get();

可选优化：使用模型作用域复用查询 #

如果该查询会被多次调用，可在模型中定义作用域简化代码：

// 在OperationAssignment模型中添加作用域方法
public function scopeWithAssignmentStats($query)
{
    return $query->select(
        'team_name',
        \DB::raw("CASE WHEN assignment = 'Appeal/Recon/Claim Review' THEN done ELSE 0 END AS Appeal"),
        \DB::raw("CASE WHEN assignment = 'IDR' THEN done ELSE 0 END AS IDR")
    );
}

调用时更简洁：

$collections = OperationAssignment::withAssignmentStats()
    ->where('serial', 6)
    ->groupBy('team_name')
    ->get();

内容的提问来源于stack exchange，提问作者Woaraka Been Mahbub