Laravel Subquery Joins: solve the problem-default pagination cannot use groupby

Subquery Joins

In Laravel online learning resources, the example of joinSub is as follows(https://laravel.com/docs/7.x/queries#joins):

/* get the subset including the user id and the latest date when the user made a post */
$latestPosts = DB::table('posts')
              ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
              ->where('is_published', true)
              ->groupBy('user_id');
/* join the subset $lastestPosts with the table users to combine the users information to the subset */
$users = DB::table('users')
        ->joinSub($latestPosts, 'latest_posts', function ($join) {
            $join->on('users.id', '=', 'latest_posts.user_id');
        })->get();

The trigger method get() is added in the end. It is very useful for pagination that need groupBy clause.

Laravel default pagination cannot include groupBy to get the collection. joinSub is one the best solutions. It is very simple: just replacing method get() with paginate().

$users = DB::table('users')
        ->joinSub($latestPosts, 'latest_posts', function ($join) {
            $join->on('users.id', '=', 'latest_posts.user_id')
        })->paginate(10);

If you’d like to specify the fields of different tables, please be careful, because the fields from the subset$latestPosts should be referenced by the subset name latest_posts; the fields from the table users will be referenced by the table name users.

$users = DB::table('users')
        ->joinSub($latestPosts, 'latest_posts', function ($join) {
            $join->on('users.id', '=', 'latest_posts.user_id')
        ->select(
           'latest_posts.*',
           'users.*' 
       })->paginate(10);

I tested it in both of local and product environments, it works well.

Medium helps me a lot, and I hope my code will be helpful for you as well.