mardi 26 juin 2018

Get most recent status for each server in laravel

I am using eloquent query builder and I am trying to get the most recent status for each server, I have a servers table that has a one to many relationship with my server_statuses table. My current 'solution' is based off the laracon online advanced eloquent presentation, but it is not returning anything from my database, attached below is my Server model, AppServiceProvider, and Controller.

What am I doing wrong here? I cant seem to figure it out.

Controller:

$recent = Server::WithLastStatusDate()
            ->OrderBy('server_id');

Model:

public function scopeWithLastStatusDate($query){

    $query ->addSubSelect('last_status_date', ServerStatus::select('created_at')
        ->whereRaw('server_id = servers.id')
        ->latest()
    );
}

AppServiceProvider:

Builder::macro('addSubSelect', function ($column, $query) {
    if (is_null($this->getQuery()->columns)) {
        $this->select($this->getQuery()->from.'.*');
    }

    return $this->selectSub($query->limit(1)->getQuery(), $column);
});

Again any help would be much appreciated, Thank you for your time!

EDIT:::

Im not the best at raw sql but here is what the previous should be in raw sql.

SELECT "servers".*, (SELECT "created_at" FROM "server_statuses" WHERE server_id = servers.id ORDER by "created_at" DESC LIMIT 1) as "last_server_status" FROM "servers" ORDER BY servers.id



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire