dpepbjp126917
2018-06-29 13:58
浏览 47
已采纳

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

Right now I am running a sub-query to get the most recent status for a server, this sub-query is returning through the variable 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(); 

What I am trying to do now is do a join on this so that it gives me the actual name of the status based on the result of this query in the statuses table. I have tried to do a simple left join but am getting the error that the last_status column isn't found.

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

Can anyone point me in the right direction on how to accomplish this?

EDIT::

Server Table:

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

Server_statuses Table:

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

statuses table:

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

What $servers looks like after sub-query:

enter image description here

Raw SQL of query:

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

EDIT 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();
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • doulianxing4015 2018-06-29 17:59
    已采纳

    Combine LEFT JOINs with a subquery WHERE clause:

    $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();
    
    打赏 评论
  • dongxifu5009 2018-06-29 14:31

    Since i'm not sure what exactly do you want to get from your query i going with a long solution and add some examples. With these tables you should have these models: Server model:

    class Server extends Model {
        public function statuses() {
            return $this->belongsToMany(Status::class, 'server_statuses');
        }
    }
    

    Status model:

    class Status extends Model {
        public function servers() {
            return $this->belongsToMany(Server::class, 'server_statuses');
        }
    }
    

    Examples: Get last status of server:

    Server::find($serverId)->statuses()->latest()->first()->status;
    

    Get all server statuses:

    Server::find($serverId)->statuses;
    

    Get server's specific status:

    Server::find($serverId)->statuses()->where('status', 'SomeStatus')->get();
    

    Get servers that have specific status:

    Server::whereHas('statuses', function ($join) use ($status) {
        return $join->where('status', $status);
    })->get();
    

    Hope you find your answer.

    打赏 评论
  • doutan8775 2018-06-29 14:52

    As far as I understand, both your Server and Status models have a OneToMany relationship to ServerStatus. In this case, you could fake a OneToOne relationship on your Server model which is selected as the latest row of serverStatuses():

    class Server
    {
        public function serverStatuses()
        {
            return $this->hasMany(ServerStatus::class, 'server_id', 'id');
        }
    
        public function latestServerStatus()
        {
            return $this->hasOne(ServerStatus::class, 'server_id', 'id')
                ->latest(); // this is the most important line of this example
                            // `->orderBy('created_at', 'desc')` would do the same
        }
    }
    
    class ServerStatus
    {
        public function server()
        {
            return $this->belongsTo(Server::class, 'server_id', 'id');
        }
    
        public function status()
        {
            return $this->belongsTo(Status::class, 'status_id', 'id');
        }
    }
    
    class Status
    {
        public function serverStatuses()
        {
            return $this->hasMany(ServerStatus::class, 'status_id', 'id');
        }
    }
    

    You can then also eager load the latest status for your servers as well as the status itself:

    Server::with('latestServerStatus.status')->get();
    

    Please be aware that $server->latestServerStatus is no collection but one object, just like in a normal OneToOne relation.

    打赏 评论

相关推荐 更多相似问题