Categories: LaravelLaravel 6

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 impro 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 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 Resent Post

If you want to fetch resent 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();

Yogesh Koli

Software engineer & Blogger lives in India, has 6+ years of experience working with the front-end and back-end web app development.

Recent Posts

3 Useful examples of using Array Map function in PHP – Best Practices

Learn how to use php array map function with easy and essential tutorial to modify arrays in php here I…

3 months ago

Working with PHP Array Filter Function – Best Practices

Learn how to use php array filter function with easy and essential tutorial to filter array in php here I…

3 months ago

How to add Access Modifiers with Constructor Parameters in TypeScript

Want to know how to refactor your Typescript class, Learn here utilising Typescript of the best and essential feature that…

3 months ago

What is Access Modifiers and how to use Access Modifiers in TypeScript ?

What is Access Modifiers in typescript, how to use Access Modifiers, when to use them, what are the advantage of…

3 months ago

Top 10 Super Useful Packages to Improve Laravel applications in 2019

This tutorial provide ultimate list of package those are top 10 on packagist and super useful to optimize your laravel…

3 months ago

How to secure pdf files In Laravel and allow access to authenticated users

This tutorial provides a perfect solution on how to protect and secure pdf file in laravel project and should only…

3 months ago