SubQueries and SubSelects: One Step Towards Raw SQL

Video Version of the Lesson

[Only for premium members]

Link to the repository

[Only for premium members]

Subselects and subqueries are powerful SQL features that aren't frequently discussed in the context of Laravel's Eloquent ORM. While raw SQL queries might be more appropriate for complex subqueries, Eloquent does provide ways to implement subselects effectively.

This lesson explores techniques for retrieving the latest post for each user in a Laravel application, comparing relationship-based approaches with subselects.

The Challenge

Our task is to load the latest record (post) for each user in our system. Our expected output should display:

• Username
• The creation date of their latest post

There are two ways to achieve the result:

1. Eloquent relationship
2. Adding a subselect

Approach 1: Eloquent Relationships

One way to accomplish this is by defining a special relationship in your User model specifically for the latest post:

app/Models/User.php:

use Illuminate\Database\Eloquent\Relations\HasOne;
use Illuminate\Database\Eloquent\Relations\HasMany;
 
class User extends Authenticatable
{
    // ...
 
    public function posts(): HasMany
    {
        return $this->hasMany(Post::class);
    }
 
    public function lastPost(): HasOne
    {
        return $this->hasOne(Post::class)->latest();
    }
}

Then in your controller:

$users = User::with('lastPost')->get();

And in the View to show the result:

@foreach ($users as $user)
    <div>{{ $user->name }}: {{ $user->lastPost->created_at }}</div>
@endforeach

But the result isn't good looking at the performance...