如何将原生SQL转换为Laravel Eloquent或查询构造器实现

各位Laravel开发者大家好，我想咨询如何将一段原生SQL查询转换为Laravel查询构造器或Eloquent ORM的实现，我已定义好相关模型、关联及数据表结构，具体信息如下：

已定义的模型关联 #

Server模型 #

public function users()
{
    return $this->belongsToMany(User::class,'server_users')->withPivot('spam','inbox');;
}

public function users_cancellation()
{
    return $this->belongsToMany(User::class,'server_user_cancellations')->withPivot('spam','inbox');;
}

User模型 #

public function servers(): BelongsToMany
{
    return $this->belongsToMany(Server::class, 'server_users');
}

public function servers_cancellation(): BelongsToMany
{
    return $this->belongsToMany(Server::class, 'server_user_cancellations');
}

相关数据表结构 #

users表 #

$table->id();
$table->string('full_name');
$table->string('username')->unique();
$table->string('password');
$table->string('type');
$table->boolean('is_active')->default(true);
$table->rememberToken();
$table->timestamps();
$table->softDeletes();

servers表 #

Schema::create('servers', function (Blueprint $table) {
    $table->id();
    $table->string('name')->default('SM');
    $table->ipAddress('ip')->unique();
    $table->string('username')->default('root');
    $table->boolean('is_active')->default(true);
    $table->softDeletes();
    $table->timestamps();
});

server_user_cancellations表 #

Schema::create('server_user_cancellations', function (Blueprint $table) {
    $table->id();
    $table->foreignIdFor(Server::class)->constrained()->cascadeOnDelete()->cascadeOnUpdate();
    $table->foreignIdFor(User::class)->constrained()->cascadeOnUpdate()->cascadeOnDelete();
    $table->boolean('spam')->nullable();
    $table->boolean('inbox')->nullable();
    $table->timestamps();
});

server_users表 #

Schema::create('server_users', function (Blueprint $table) {
    $table->id();
    $table->foreignIdFor(Server::class)->constrained()->cascadeOnDelete()->cascadeOnUpdate();
    $table->foreignIdFor(User::class)->constrained()->cascadeOnUpdate()->cascadeOnDelete();
    $table->boolean('spam')->nullable();
    $table->boolean('inbox')->nullable();
    $table->timestamps();
});

待转换的原生SQL #

if ($request->wantsJson()) {
  $servers = DB::select('
  SELECT 
    s.name,
    s.id,
    s.ip,
    (SELECT count(id) 
      FROM server_user_cancellations 
      where server_id = s.id and user_id=?
    ) as exist
  FROM servers s
  WHERE s.id IN
    (SELECT  distinct server_id from server_users  where user_id=?)
    AND s.is_active=true
    AND s.is_installed=true
    AND s."deleted_at" is null', 
  [auth()->id(), auth()->id()]
);

可直接使用的转换后代码 #

方案1：Eloquent关联实现（推荐） #

复用已定义的模型关联，逻辑更简洁，框架会自动处理软删除过滤，性能优于子查询写法：

if ($request->wantsJson()) {
    $userId = auth()->id();
    $servers = auth()->user()
        ->servers()
        ->where('is_active', true)
        ->where('is_installed', true)
        ->withCount([
            'users_cancellation as exist' => fn($query) => $query->where('user_id', $userId)
        ])
        ->select('id', 'name', 'ip')
        ->get();
}

注意：你提供的servers表迁移代码中没有is_installed字段，原SQL包含该字段过滤条件，请上线前确认字段是否存在，避免报错。

方案2：查询构造器实现（和原生SQL逻辑1:1对齐） #

如果需要完全匹配原生SQL的执行逻辑，可使用查询构造器写法：

if ($request->wantsJson()) {
    $userId = auth()->id();
    $servers = DB::table('servers as s')
        ->select('s.name', 's.id', 's.ip')
        ->selectRaw('(SELECT count(id) FROM server_user_cancellations WHERE server_id = s.id AND user_id = ?) as exist', [$userId])
        ->whereIn('s.id', function ($query) use ($userId) {
            $query->distinct()
                ->select('server_id')
                ->from('server_users')
                ->where('user_id', $userId);
        })
        ->where('s.is_active', true)
        ->where('s.is_installed', true)
        ->whereNull('s.deleted_at')
        ->get();
}

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