Laravel eloquent on how to order a collection using accessor on appends array
I have an Eloquent model:
class Song extends Eloquent {
protected $table = 'mg_songs';
protected $hidden = array('events');
protected $appends = array('lastDate');
public function events()
{
return $this->belongsToMany('Event', 'song_event');
}
public function getLastDateAttribute()
{
if (!$this->events) return null;
return $this->events[0]->date->formatLocalized('%d.%m.%Y (%a, %H)');
}}
Is it possible to sort the "lastdate" field the same way as the db field:
$songs->orderBy('title', 'asc'); - works
$songs->orderBy('lastDate', 'desc'); - doesn't works
Could there be a simple answer?
Editorial staff:
My db structure (only required fields), with many-to-many:
event table
event_id
date
song table
song_id
title
a table with a summary song_event
ID
song_id
event_id
SQL query:
SELECT s.title, (SELECT MAX(e.date) FROM events e JOIN song_event se ON (e.id = se.event_id) WHERE se.song_id = s.id) AS s_date FROM mg_songs s ORDER BY s_date desc
source to share
You can sort the resulting set using accessories, obviously the request cannot be ordered as it is not in the db.
$songs = Song::all(); // get the result
$songs->sortByDesc('lastDate'); // sort using collection method
// or ascending:
$songs->sortBy('lastDate');
You can achieve the same by using joins
if you prefer to do it in the db call (this is better from a performance standpoint).
Another thing: you are using if( ! $this->events)
which will soon cause problems.
Check it:
// hasOne / belongsTo / morphTo etc - single relations
$model->relation; // returns related model OR null -> evaluates to false
// BUT for hasMany / belongsToMany etc - multiple results
$model->relation; // always returns a collection, even if empty, evaluates to true
So, change this if
to:
public function getLastDateAttribute()
{
if ( ! count($this->events)) return null;
return $this->events[0]->date->formatLocalized('%d.%m.%Y (%a, %H)');
}}
source to share