CODEWITHSUNDEEP
phpmysqllaraveleloquentrelation
SirInfant
SirInfant

Reputation: 323

Laravel 5: Eloquent - order by relation when returning single record

im trying build query in eloquent with data sorted by relation. Imagine this DB structure:

TABLE: Station

id
name
...

TABLE: station_status:

id
station_id
status_type_id
date
...

TABLE: status_type:

id
description
...

MODELS

class Station extends \Eloquent
{
    public function stationStatus() {
        return $this->hasMany('App\StationStatus', 'station_id', 'id');
    }
}

class StationStatus extends \Eloquent
{
    public function statusType() {
        return $this->hasOne('App\StatusType', 'id', 'status_type_id');
    }
}

class StatusType extends \Eloquent
{
    ...
}

Now the question. How can i query Station model by station ID, but sort by related status types description?

So far i have:

// This just do not work
$query = Station::join('station_status', 'station.id', '=',   'station_status.station_id')
    ->join('status_type', 'station_status.status_type_id', '=', 'status_type.id')
    ->orderBy('status_type.description', 'ASC')
    ->select(['stations.*'])
    ->with(['stationStatus.statusType']
    ->find(110);

I think the problem is that i'm not returning collection but only one item using find() method, how can i overcome this problem?

Many thanks for any help !

Upvotes: 2

Views: 681

Answers (1)

DevK
DevK

Reputation: 9962

Try this:

$query = Station::with(['stationStatus' => function($q){
    $q->join('status_type', 'station_status.status_type_id', '=', 'status_type.id')
    ->orderBy('status_type.description', 'ASC');
}])->find(110);

With is a convienent way of getting the related objects, but it doesn't perform a join. It performs a new query and attaches all the elements to your collection. You can add your own logic to the query, like I did with $q->orderBy(...).

Upvotes: 1

Related Questions