Skip to main content

By Jesse Schutt

Sorting Parent Eloquent Models by a Child Relationship

10.06.17

2 minutes to read

code

Recently I had a situation in a Laravel app where I needed to sort a collection of parent models by a particular value in a BelongsTo() relationship field.

It’s fairly simple to sort the opposite direction, meaning, to sort all the related models that belong to a parent model.

For example, say I have a Shirt model that can have many Tags:

By using an orderBy() on the tags() relationship I can get all the child Tags for a parent Shirt ordered alphabetically.

<?php 

namespace App;

use Illuminate\Database\Eloquent\Model;

class Shirt extends Model {
  public function tags() {
    Return $this->belongsToMany(\App\Tag::class)->orderBy(‘title’);
  }
}

Now every time I access the tags() relationship on a Shirt model, any related tags will automatically be sorted by title.

But, what happens if I have a relationship and want to sort the parent by a value in the related model instead? That’s a little more tricky.

Setting the stage

Say I have a Shirt model that has a single Size assigned to it via the size() BelongsTo() relationship.

In Laravel it looks like this:

<?php 

namespace App;

use Illuminate\Database\Eloquent\Model;

class Shirt extends Model {
  public function size() {
    Return $this->belongsTo(\App\Size::class);
  }
}

Since I want to sort the parent by the child, it doesn’t help me to add an orderBy() on the size() relationship since that would only sort the related models... and there is only one Size per Shirt!

As an aside, I can’t simply sort alphabetically by the Size title, because sizes don’t naturally order alphabetically; XS should come before S, and M before L...

To manually sort the sizes I need a different approach. So, I added a sort column on the sizes table and manually add the order I wish to display the sizes in. With this approach I have something that is easily sortable.

Once the sort column is populated I have a way to sort the sizes in the correct order, but how do I translate it to the parent Shirt models in order to sort them the appropriate size order?

Here's how I do it:

\App\Shirt::with('size')
    ->select(‘shirts.*', \DB::raw('(SELECT sort FROM sizes WHERE shirts.size_id = sizes.id ) as sort'))
    ->orderBy('sort')
    ->get();

Now we have all the Shirt models ordereed by the sort field on their Size related model, and we did it on the query-side of things, instead of resorting to PHP sorting!

Want to read more tips and insights on working with a website development team that wants to help your organization grow for good? Sign up for our bimonthly newsletter.

By Jesse Schutt

Director of Engineering

Jesse is our resident woodworker. His signature is to find the deeper meaning in a project and the right tool for the job.

Keep on reading