By continuing your visit to this site, you accept the use of cookies. Read more

Scout APM helps PHP developers pinpoint N+1 queries, memory leaks & more so you can troubleshoot fast & get back to coding faster. Start your free 14-day trial today .

« back — written by Brent on November 09, 2019

Relationship issues

Or in other words, dealing with complex database relations and Laravel models.

Recently I had to deal with a complex performance issue in one of our larger Laravel projects. Let me quickly set the scene.

We want an admin user to see an overview of all people in the system in a table, and we want a column in that table to list which contracts are active at that moment for each person.

The relation between Contract and Person is as follows:

Contract > HabitantContract > Habitant > Person

I don't want to spend too much time going into details as to how we came to this relationship hierarchy. It's important for you to know that, yes, this hierarchy is important for our use cases: a Contract can have several Habitants , which are linked via a pivot model HabitantContract ; and each Habitant has a relation to one Person .

Since we're showing an overview of all people, we'd like to do something like this in our controller:

class PeopleController { public function index () { $people = PersonResource :: collection ( Person :: paginate ()); return view ( 'people.index' , compact ( 'people' )); } }

Let's make clear that this is an oversimplified example, though I hope you get the gist. Ideally, we'd want our resource class to look something like this:

class PersonResource extends JsonResource { public function toArray ($request) : array { return [ 'name' => $this ->name, 'active_contracts' => $this ->activeContracts -> map ( function ( Contract $contract) { return $contract->contract_number; }) -> implode ( ', ' ), ]; } }

Notice especially the Person::activeContracts relation. How could we make this work?

A first thought might be by using a HasManyThrough relation, but remember that we're 4 levels deep in our relation hierarchy. Besides that, I find HasManyThrough to be very confusing.

We could query the contracts on the fly, one-by-one per person. The issue with that is that we're introducing an n+1 issue since there'll be an extra query per person. Imagine the performance impact if you're dealing with more than just a few models.

One last solution that came to mind was to load all people, all contracts, and map them together manually. In the end that's exactly what I ended up doing, though I did it in the cleanest possible way: using custom relations.

Let's dive in.

# Configuring the Person model

Since we want our $person->activeContracts to work exactly like any other relation, there's little work to be done here: let's add a relation method to our model, just like any other.

class Person extends Model { public function activeContracts () : ActiveContractsRelation { return new ActiveContractsRelation ( $this ); } }

There's nothing more to do here. Of course we're only starting, since we haven't actually implemented ActiveContractsRelation !

# The custom relation class

Unfortunately there's no documentation on making your own relation classes. Luckily you don't need much to learn about them: some code-diving skills and a little bit of time gets you pretty far. Oh an IDE also helps.

Looking at the existing relation classes provided by Laravel, we learn that there's one base relation that rules them all: Illuminate\Database\Eloquent\Relations\Relation . Extending it means you need to implement some abstract methods.

class ActiveContractsRelation extends Relation { public function addConstraints () { } public function addEagerConstraints (array $models) { } public function initRelation (array $models, $relation) { } public function match (array $models, Collection $results, $relation) { } public function getResults () { } }

The doc blocks get us on the way, though it's not always entirely clear what needs to happen. Again we're in luck, Laravel still has some existing relation classes where we can look to.

Let's go through building our custom relation class step by step. We'll start by overriding the constructor and adding some type hints to the existing properties. Just to make sure, the type system will prevent us from making stupid mistakes.

The abstract Relation constructor requires both specifically for an eloquent Builder class, as well as the parent model the relationship belongs to. The Builder is meant to be the base query object for our related model, Contract , in our case.

Since we're building a relation class specifically for our use case, there's no need to make the builder configurable. Here's what the constructor looks like:

class ActiveContractsRelation extends Relation { protected $query; protected $parent; public function __construct ( Person $parent) { parent :: __construct ( Contract :: query (), $parent); } }

Note that we type hint $query both with the Contract model as well as the Builder class. This allows IDEs to provide better autocompletion, such as custom scopes defined on the model class.

We've got our relation constructed: it will query Contract models, and use a Person model as its parent. Moving on to building our query.

This is where the addConstraints method come in. It will be used to configure the base query. It will set up our relation query specifically to our needs. This is the place where most business rules will be contained:

We only want active contracts to show up

We only want to load active contracts that belong to a specified person (the $parent of our relation)

of our relation) We might want to eagerly load some other relations, but more on that later.

Here's what addConstraints looks like, for now:

class ActiveContractsRelation extends Relation { public function addConstraints () { $this ->query -> whereActive () -> join ( 'contract_habitants' , 'contract_habitants.contract_id' , '=' , 'contracts.id' ) -> join ( 'habitants' , 'habitants.id' , '=' , 'contract_habitants.habitant_id' ); } }

Now I do assume that you know how basic joins work. Though I will summarize what's happening here: we're building a query that will load all contracts and their habitants , via the contract_habitants pivot table, hence the two joins.

One other constraint is that we only want active contracts to show up; for this we can simply use an existing query scope provided by the Contract model.

With our base query in place, it's time to add the real magic: supporting eager loads. This is where the performance wins are: instead of doing one query per person to load its contracts, we're doing one query to load all contracts, and link these contracts to the correct people afterwards.

This is what addEagerConstraints , initRelation and match are used for. Let's look at them one by one.

First the addEagerConstraints method. This one allows us to modify the query to load in all contracts related to a set of people. Remember we only want two queries, and link the results together afterwards.

class ActiveContractsRelation extends Relation { public function addEagerConstraints ( array $people) { $this ->query-> whereIn ( 'habitants.contact_id' , collect ($people)->pluck( 'id' ) ); } }

Since we joined the habitants table before, this method is fairly easy: we'll only load contracts that belong to the set of people provided.

Next the initRelation . Again this one is rather easy: its goal is to initialise the empty activeContract relationship on every Person model, so that it can be filled afterwards.

class ActiveContractsRelation extends Relation { public function initRelation ( array $people, $relation) { foreach ($people as $person) { $person-> setRelation ( $relation, $this ->related-> newCollection () ); } return $people; } }

Note that the $this->related property is set by the parent Relation class and it's a clean model instance of our base query so in other words, an empty Contract model:

abstract class Relation { public function __construct ( Builder $query, Model $parent) { $this ->related = $query-> getModel (); } }

Finally we arrive at the core function that will solve our problem: linking all people and contracts together.

class ActiveContractsRelation extends Relation { public function match ( array $people, Collection $contracts, $relation) { if ($contracts-> isEmpty ()) { return $people; } foreach ($people as $person) { $person-> setRelation ( $relation, $contracts-> filter ( function ( Contract $contract) use ($person) { return $contract->habitants-> pluck ( 'person_id' )-> contains ($person->id); }) ); } return $people; } }

Let's walk through what's happening here: on the one hand we've got an array of parent models, the people; on the other hand we've got a collection of contracts, the result of the query executed by our relation class. The goal of the match function is to link them together.

How to do this? It's not that difficult: loop over all people, and search all contracts that belong to each one of them, based on the habitants linked to that contract.

Almost done? Well… there's one more issue. Since we're using the $contract->habitants relation, we need to make sure it is also eagerly loaded, otherwise we just moved the n+1 issue instead of solving it. So it's back to the addEagerConstraints method for a moment.

class ActiveContractsRelation extends Relation { public function addEagerConstraints ( array $people) { $this ->query -> whereIn ( 'habitants.contact_id' , collect ($people)->pluck( 'id' ) ) -> with ( 'habitants' ) -> select ( 'contracts.*' ); } }

We're adding the with call to eagerly load all habitants, but also note the specific select statement. We need to tell Laravel's query builder to only select the data from the contracts table, because otherwise the related habitant data will be merged on the Contract model, causing it to have the wrong ids and what not.

Finally we need to implement the getResults method, which simply executes the query:

class ActiveContractsRelation extends Relation { public function getResults () { return $this ->query->get(); } }

And that's it! Our custom relation can now be used like any other Laravel relation. It's an elegant solution to solving a complex problem the Laravel way.