Complete guide of using Laravel 6 Eloquent Subquery Enhancements

If you know Laravel 6.0 comes with new enhancements and improvements feature and one of the great feature is Addition of Eloquent subquery. In this tutorial I will provide complete guide on using it with practical examples

let me give you clear ideas what is Subquery and what is the exact use of the Subquery in SQL

If you know in SQL we can write query along with the other queries which could be for particular column or from table or while using order by clause this is what we called a Subqueries

Recommended to Read – Laravel 6 Release New Features and Updates

In laravel we have Eloquent ORM and it is a ActiveRecord implementation for working with database.

So While working with the database we certainly want to fetch records from the complicate relationships.

And this is where Eloquent Subquery comes into play and it is very helpful to improve application performance

In Earlier version of laravel there were some limitation while handling Eloquent Subquery from laravel.

But now with Laravel 6 it is more improved thanks to Jonathan Reinink who contributed to laravel framework and given ability to add select a subquery using addSelect function.

“Always Avoid to use multiple queries and optimize database load instead prefer to use Subqueries depending on your needs”

What is Eloquent Subquery?

Eloquent Subquery is basically a queue within a query which allows you to pull information from related tables in a single query.

Why we should use Eloquent Subqueries?

It is very simple to answer, use this feature whenever you want fetch records from the related tables.

Also the important needs of subqueries is avoiding database load by preventing or limiting number of queries.

Okay enough theory let me give you some practical example so that you will understand and see how useful it is to use in your laravel projects.

Practical Examples of Using Eloquent Subqueries

Using Eloquent subqueries in Laravel is incredibly simple it avoids lot of complexity from the database queries.

Fetch User Last Activity Date:

Let’s assume that your application has a feature to maintain user activities and to store the activities details you will have separate database table for example user and activities table.

Now let’s fetch last activity by particular user:

App\User::addSelect(['last_activity' => Activities::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->orderBy('created_at', 'desc')
    ->limit(1)])
    ->find(1);

Or you might want to fetch all users with last activity

App\User::addSelect(['last_activity' => Activities::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->orderBy('created_at', 'desc')
    ->limit(1)])
    ->get();

You can also use closure function in place of Eloquent model but you know using model is always easy and convenient and also it is easy to read.

Here is how you can use closure function for eloquent subqueries:

App\User::addSelect(['last_activity' => function ($query) {
    $query->select('created_at')
        ->fromt('activities')
        ->whereColumn('user_id', 'users.id')
        ->orderBy('created_at', 'desc')
        ->limit(1);
}])->get();

Get User Recent Post

If you want to fetch recent post id of a particular user then you can use subquery as showing below:

App\User::addSelect(['last_post_id' => Post::select('id')
    ->whereColumn('user_id', 'users.id')
    ->orderBy('created_at', 'desc')
    ->limit(1)])
    ->find(1);

So this will return an user along with the last_post_id column

We can also write same example using closure function:

App\User::addSelect(['last_post_id' => function ($query) {
    $query->select('created_at')
        ->fromt('posts')
        ->whereColumn('user_id', 'users.id')
        ->orderBy('created_at', 'desc')
        ->limit(1);
}])->find(1);

Apply Eloquent Subquery to OrderBy clause:

As I said earlier we can apply subqueries to the order by clause as well right?

So basically you may want to filter records from the table depending on the column of different table

For example you may want to fetch latest active user from the system based on there last activity

Here is how you can do that with laravel Eloquent Subquery

App\User::orderByDesc(Activities::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->latest()
    ->limit(1))
    ->get();

So the above example will return list of active user in descending order if you want in ascending order just change orderByDesc() to orderBy().

Here is how you can write same example with ascending order using closure function

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