Laravel – Selecting columns when eager loading relations.

Eloquent, the ORM native to Laravel can be powerful when used in the right hands. However, if you’re unaware of how things work beneath the hood you may, at times, find yourself struggling. I’m often asked questions related to how eager loading works, and selecting columns when using eager loading seems to raise a common issue.

Consider the following: When selecting columns and using eager loading (via the ‘with’ method) you may find that your query returns 0 results, regardless of whether relations exist or not. This may seem confusing at first, but when you understand  how eloquent implements eager loading, it begins to make sense.

In a nutshell here is what happens:

1. Eloquent runs an SQL query to fetch the primary entity (or entities).
2. Eloquent runs a separate query for each eager load. Bear in mind, that to do this it needs to know how the entities are related. In other words it needs to know the specific columns and, importantly, the values on which to filter.

Let’s look at an example using the below data model.

class Organisation extends Model {

	/**
	 * Many to Many relation
	 *
	 * @return Illuminate\Database\Eloquent\Relations\BelongsToMany
	 */
	public function documents()
	{
		return $this->belongsToMany(Document::class, 'organisation_has_documents');
	}
}

class Document extends Model {
	/**
	 * Many to Many relation
	 *
	 * @return Illuminate\Database\Eloquent\Relations\BelongsToMany
	 */
	public function organisations()
	{
		return $this->belongsToMany(Organisation::class, 'organisation_has_documents');
	}

}

*Note: The code above assumes you already have a database in place and that the pivot table is called ‘organisationhasdocuments’. It also assumes both models are within the same namespace.*

Tracking the queries generated by Eloquent.

Now let’s run an eager load query against this model, using Eloquent’s logger to track the queries generated.

\Illuminate\Support\Facades\DB::enableQueryLog();

$org = \App\Models\Organisation::with('documents')
    ->find(1);

var_export(\Illuminate\Support\Facades\DB::getQueryLog());

In this instance, var_export() should produce the following:

array(
        0 => array(
            'query' => 'select * from `organisations` where `organisations`.`id` = ? limit 1',
            'bindings' => array(0 => 1,),
            'time' => 0.23000000000000001,
        ),
        1 => array(
            'query' => 'select `documents`.*, `organisation_has_documents`.`organisation_id` as `pivot_organisation_id`, `organisation_has_documents`.`document_id` as `pivot_document_id` from `documents` inner join `organisation_has_documents` on `documents`.`id` = `organisation_has_documents`.`document_id` where `organisation_has_documents`.`organisation_id` in (?)',
            'bindings' => array(0 => 1),
            'time' => 0.27000000000000002,
        ),
    )

As you can see two queries were executed. Just to clarify, here’s what all of the constituent parts of the above arrays mean:

• query – The SQL query that was executed.
• bindings – The values that were filtered against (read up on Prepared statements if this is alien to you).
• time – How long the query took to run.

With that understanding then, it’s possible to determine that the first query returns the Organisation which has an ID of 1 (the value passed to ‘find()’). And the second query returns all documents linked to that organisation.

Using eager loading the wrong way.

Now let’s try that again, but lets select a column or two (excluding the key that defines the relationship between the two entities).

 \Illuminate\Support\Facades\DB::enableQueryLog();

$org = \App\Models\Organisation::with('documents')
    ->select(['name', 'created_at'])
    ->find(1);
    
var_export(\Illuminate\Support\Facades\DB::getQueryLog());

This time the output is slightly different:

    array(
        0 => array(
            'query' => 'select `name`, `created_at` from `organisations` where `organisations`.`id` = ? limit 1',
            'bindings' => array(0 => 1,),
            'time' => 0.29999999999999999,
        ),
        1 => array(
            'query' => 'select `documents`.*, `organisation_has_documents`.`organisation_id` as `pivot_organisation_id`, `organisation_has_documents`.`document_id` as `pivot_document_id` from `documents` inner join `organisation_has_documents` on `documents`.`id` = `organisation_has_documents`.`document_id` where `organisation_has_documents`.`organisation_id` in (?)',
            'bindings' => array(0 => null),
            'time' => 0.35999999999999999,
        ),
    )

Whilst the queries are largely the same, you’ll notice the second query is filtering on NULL (check the bindings value). This is due to the fact that the second query is dependent on the values returned by the first. Since the first query is not fetching the ID, Eloquent is not aware of it and instead filters on NULL. As there are no organisations with an ID of null, 0 documents are returned.

To fix this you’ll need to include the key/column which relates Organisations and Documents (‘id’ in this case).

Using eager loading the right way.

 \Illuminate\Support\Facades\DB::enableQueryLog();

$org = \App\Models\Organisation::with('documents')
    ->select(['id', 'name', 'created_at'])
    ->find(1);
    
var_export(\Illuminate\Support\Facades\DB::getQueryLog());

If you run the query above, you’ll notice that the second query is filtering on 1, rather than on NULL and that the issue has been resolved.

To conclude then, when you are using eager loading in conjunction with column selection, always include the column(s) used to define the relationship. Whilst this will mostly be the ‘id’ column, remember that’s not always going to be the case. And in fact, in some cases (polymorphic relations for example) the relationship may be defined by more than one column.