php - 是否可以对子查询创建的变量运行连接？

Question

现在我正在运行一个子查询来获取服务器的最新状态，这个子查询通过变量 last_status 返回。

 //This is ran when WithLastStatusDate() is called
$query->addSubSelect('last_status', ServerStatus::select('status_id')
    ->whereRaw('server_id = servers.id')
    ->latest()
);

$servers = Server::WithLastStatusDate()
    ->OrderBy('servers.id', 'desc')
    ->where('servers.isPublic', '=', 1)
    ->get(); 

我现在要做的是对此进行连接，以便它根据状态表中的查询结果为我提供状态的实际名称。我尝试执行简单的左连接，但收到错误消息，指出未找到 last_status 列。

$servers = Server::WithLastStatusDate()
    ->OrderBy('servers.id', 'desc')
    ->where('servers.isPublic', '=', 1)
    ->leftjoin('statuses','servers.last_status', '=', 'statuses.id')
    ->get(); 

任何人都可以为我指出正确的方向来实现这一点吗？

编辑::

服务器表:

 Schema::create('servers', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->string('url');
            $table->boolean('isPublic');
            $table->timestamps();
        });

服务器状态表:

Schema::create('server_statuses', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('server_id')->unsigned();
            $table->foreign('server_id')->references('id')->on('servers')->onDelete('cascade');
            $table->integer('status_id')->unsigned();
            $table->foreign('status_id')->references('id')->on('statuses');
            $table->timestamps();
        });

状态表:

Schema::create('statuses', function (Blueprint $table) {
    $table->engine = 'InnoDB';
    $table->increments('id');
    $table->string('key');
    $table->string('status');
    $table->timestamps();
});

子查询后 $servers 的样子:

查询的原始 SQL:

select `servers`.*, (select `status_id` from `server_statuses` where server_id = servers.id order by `created_at` desc limit 1) as `last_status` from `servers` where `servers`.`isPublic` = '1' order by `servers`.`id` desc

编辑 2::

    $servers = DB::table('servers as sv')
        ->join('server_statuses as ss', 'sv.id', '=', 'ss.server_id')
        ->join('statuses as st', 'ss.status_id', '=', 'st.id')
        ->WithLastStatus()
        ->OrderBy('servers.id', 'desc')
        ->where('servers.isPublic', '=', 1)
        ->get();

Answer 1

将 LEFT JOIN 与子查询 WHERE 子句相结合:

$servers = Server::select('servers.*', 'statuses.status as status_name')
    ->leftJoin('server_statuses', function($join) {
        $join->on('server_statuses.server_id', '=', 'servers.id')
            ->where('server_statuses.id', function($query) {
                $query->select('id')
                    ->from('server_statuses')
                    ->whereColumn('server_id', 'servers.id')
                    ->latest()
                    ->limit(1);
            });
    })
    ->leftJoin('statuses', 'statuses.id', '=', 'server_statuses.status_id')
    ->where('servers.isPublic', '=', 1)
    ->orderBy('servers.id', 'desc')
    ->get();