Relationship structure

you've got it nailed! user->experiences is a great name for it.

• jobs
• courses
• volunteer work.

do you need any specific help

Thanks.

I've now got my tables set up like:

users
    id - integer
    name - string

projects
    id - integer
    name - string

jobs
    id - integer
    name - string

experiences
    id - integer
    user_id - integer
    experiencable_id - integer
    experiencable_type - string
    start_date - date
    end_date - date

With my Models defined like:

class User
{
    public function experiences(){
        return $this->hasMany(\App\Experience::class);
    }
}

// Project and Job actually use a trait for defining the `experiences` relationship, along with some other shared bits
class Project
{
    public function experiences(){
        return $this->morphMany(\App\Experience::class, 'experiencable');
    }
}

class Job
{
    public function experiences(){
        return $this->morphMany(\App\Experience::class, 'experiencable');
    }
}

class Experience
{
    public function experiencable(){
        return $this->morphTo();
    }

    public function user(){
        return $this->belongsTo(\App\User::class);
    }

    public function scopeOrderByDate($query, $dir = 'desc'){
        $dir = (($dir == 'asc') ? $dir : 'desc');

        // order by end date, where null values are highest, but only if start date exists - otherwise it's lowest (ie. if it has a start date, but a null end date, it is classed as current)
        return $query->orderBy(DB::raw('(' . $this->getTable() . '.date_end IS NULL AND ' . $this->getTable() . '.date_start IS NOT NULL)'), $dir)
            ->orderBy($this->getTable() . '.date_end', $dir)
            ->orderBy($this->getTable() . '.date_start', $dir);
    }
}

So a user can have multiple experiences and an experience can be a Project or Job. An experience has a start date and an end date, which is relative to the user. Multiple users could have their own experiences, linking to the same Project and Job as another user's experience, but they could have different dates (ie. two users had the same job but worked there at different times).

As seen above, I can order by the experience's dates easily. My new issue is trying to get a list of experiences for a user, ordered by the Project/Job name.

Is there a away of sorting Experiences by their experiencable's name, using SQL / Eloquent?

You can do it after the models are loaded by using the sortBy() method on the Collection.

User::with('experiences')->get()->sortBy('experiences.name');

Ugg, I lost my reply.

Short answer.

Quick and dirty.

Use the eloquent collection sortBy()

or via eloquent

          User::all()->load(['experiences => function($q) {$q->orderBy('experience_type', 'desc');}]); 

one thing you may want to do is update the relations themselves so they are always ordered. easy to do let me know if you want to.

Thanks guys.

I'm hoping to do the sorting in the query itself, rather than using the collection sortBy method, for speed, and reusability (with a model scope).

robrogers3, I think you misunderstood; I'm wanting to sort experiences by the name column on the project and jobs tables.

To throw another spanner in the works, what I'm actually trying to achieve is sorting on the experience dates, then the Project or Job's name, if the dates are the same. Like the query scope above, but with the name included. Something like:

    public function scopeOrderByDate($query, $dir = 'desc'){
        $dir = (($dir == 'asc') ? $dir : 'desc');

        // order by end date, where null values are highest, but only if start date exists - otherwise it's lowest (ie. if it has a start date, but a null end date, it is classed as current)
        return $query->orderBy(DB::raw('(' . $this->getTable() . '.date_end IS NULL AND ' . $this->getTable() . '.date_start IS NOT NULL)'), $dir)
            ->orderBy($this->getTable() . '.date_end', $dir)
            ->orderBy($this->getTable() . '.date_start', $dir)
            // sorting by the name column here - but I have no way of knowing the table name
            ->orderBy('[projects|jobs].name', $dir);
    }