Eloquent Subquery Enhancements in Laravel 6.0

Eloquent Subquery Enhancements in Laravel 6.0

SEPTEMBER 04, 2019 / JONATHAN REININK

If you’ve been following my work for any length of time, you know that I am a big fan of pushing more work in our Laravel applications to the database layer. By doing more work in the database we can often reduce the number of database queries we make, reduce the amount of memory our applications use, and reduce the amount of time required by Eloquent to process our models. This can result in some pretty significant performance wins.

One excellent way to push more work to the database is by using subqueries. Subqueries allow you to run nested queries within another database query. This can be a powerful way to retrieve ancillary model data, without making any additional database queries, when it’s not possible to do via a relationship. You can also use subqueries in order by statements, where statements, and other database clauses.

During my Laracon US 2019 talk, I made reference to a couple of query builder macros I’ve been using that make it easier to use subqueries in Laravel. I’ve since submitted three pull requests to Laravel to add these to the core framework.

Here’s an overview of each:

“Select” subqueries

Pull request #29567 adds support for subqueries to both the select() and addSelect() query builder methods.

For example, let’s imagine that we have a table of flight destinations and a table of flights to destinations. The flights table contains an arrived_at column which indicates when the flight arrived at the destination.

Using the new subquery select functionality in Laravel 6.0, we can select all of the destinations and the name of the flight that most recently arrived at that destination using a single query:

return Destination::addSelect(['last_flight' => Flight::select('name')
    ->whereColumn('destination_id', 'destinations.id')
    ->orderBy('arrived_at', 'desc')
    ->limit(1)
])->get();

Notice how we’re using Eloquent to generate the subquery here. This makes for a nice, expressive syntax. That said, you can also do this using the query builder:

return Destination::addSelect(['last_flight' => function ($query) {
    $query->select('name')
        ->from('flights')
        ->whereColumn('destination_id', 'destinations.id')
        ->orderBy('arrived_at', 'desc')
        ->limit(1);
}])->get();

“Order by” subqueries

In addition, pull request #29563 makes it possible to use subqueries in the query builder’s orderBy() method. Continuing our example above, we can use this to sort the destinations based on when the last flight arrived at that destination.

return Destination::orderByDesc(
    Flight::select('arrived_at')
        ->whereColumn('destination_id', 'destinations.id')
        ->orderBy('arrived_at', 'desc')
        ->limit(1)
)->get();

As with selects, you can also use the query builder directly to create the subquery. For example, maybe you want to order users based on their last login date:

return User::orderBy(function ($query) {
    $query->select('created_at')
        ->from('logins')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->limit(1);
})->get();

“From” subqueries

Finally, pull request #29602 makes it possible to use subqueries within the query builder’s from() method. These are sometimes called derived tables.

For example, maybe you want to calculate the average total donations made by users in your application. However, in SQL it’s not possible to nest aggregate functions:

AVG(SUM(amount))

Instead, we can use a from subquery to calculate this:

return DB::table(function ($query) {
    $query->selectRaw('sum(amount) as total')
        ->from('donations')
        ->groupBy('user_id');
}, 'donations')->avg('total');

You probably won’t need to use this every day, but when you do need it, it’s indispensable.

One breaking change to be aware of if you’re using Eloquent outside of Laravel is a signature change to the table() method on the Illuminate/Database/Capsule/Manager object. It’s been changed from table($table, $connection = null) to table($table, $as = null, $connection = null).

Learn more

If you’re interested in learning more about subqueries and other advanced database techniques, be sure to follow my blog, and also watch my Laracon US 2019 talk.

At Laracon I also announced a new video course I’m working on called Eloquent Performance Patterns. My goal with this course is to teach Laravel developers how to drastically improve the performance of their Laravel applications by pushing more work to the database layer, all while still using the Eloquent ORM. Be sure to join the mailing list for that if you’re interested!

Filed in: Laravel Tutorials / Eloquent