如何将现有MySQL查询转换为Laravel查询构建器语句？

嘿，我帮你把这段MySQL查询转换成Laravel支持的写法，两种方式都给你列出来，你可以根据项目情况选择：

方式一：使用DB门面（无需模型） #

这种方式直接用Laravel的数据库门面来构建查询，适合还没创建对应模型的场景：

use Illuminate\Support\Facades\DB;

$userAttributes = DB::table('radcheck as c')
    ->select([
        'c.username',
        DB::raw('max(case when c.attribute = "Cleartext-Password" then c.value end) as password'),
        DB::raw('max(case when c.attribute = "Expiration" then c.value end) as expiration'),
        DB::raw('max(case when c.attribute = "ChilliSpot-Max-Total-Octets" then c.value end) as quta'),
        DB::raw('max(case when c.attribute = "Simultaneous-Use" then c.value end) as simul'),
        DB::raw('max(case when c.attribute = "Max-All-Session" then c.value end) as session'),
        DB::raw('max(c.adsoyad) as realname'),
        DB::raw('min(c.dtarih) as birthdate'),
        DB::raw('min(c.telefon) as phone'),
        DB::raw('min(c.tcno) as tc'),
        DB::raw('max(c.email) as email'),
        DB::raw('min(c.id) as id')
    ])
    ->groupBy('c.username')
    ->get();

方式二：使用Eloquent模型（推荐） #

如果你已经为radcheck表创建了Radcheck模型，写法会更简洁：

首先确保模型的表名设置正确（因为Laravel默认会复数化表名，所以需要手动指定）：

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

use Illuminate\Database\Eloquent\Model;

class Radcheck extends Model
{
    protected $table = 'radcheck'; // 指定表名
    public $timestamps = false; // 如果表没有created_at/updated_at字段，需要关闭自动时间戳
}

然后就可以用模型查询：

use App\Models\Radcheck;

$userAttributes = Radcheck::query()
    ->select([
        'username',
        DB::raw('max(case when attribute = "Cleartext-Password" then value end) as password'),
        DB::raw('max(case when attribute = "Expiration" then value end) as expiration'),
        DB::raw('max(case when attribute = "ChilliSpot-Max-Total-Octets" then value end) as quta'),
        DB::raw('max(case when attribute = "Simultaneous-Use" then value end) as simul'),
        DB::raw('max(case when attribute = "Max-All-Session" then value end) as session'),
        DB::raw('max(adsoyad) as realname'),
        DB::raw('min(dtarih) as birthdate'),
        DB::raw('min(telefon) as phone'),
        DB::raw('min(tcno) as tc'),
        DB::raw('max(email) as email'),
        DB::raw('min(id) as id')
    ])
    ->groupBy('username')
    ->get();

关键说明： #

• 原SQL中的case聚合逻辑需要用DB::raw()包裹，因为这是原生SQL语法，Laravel查询构建器没有对应的方法直接生成
• groupBy('username')完全对应原SQL的分组逻辑
• 执行get()会返回Laravel集合，你可以用集合的各种方法（比如map()、first()）快速处理结果

内容的提问来源于stack exchange，提问作者Muh