How to use Laravel whereColumn with Subquery?

Question

After spending hours on this, finally I felt to post this question here. I am trying to join tables. Due to some circumstances, I have to use the query in the same way that I have pasted below.

select posts.id,
       last_user_comments.created_at
from posts
         left join comments as last_user_comments
                   on (select comments.id
                       from comments
                                join users on users.id = comments.user_id
                                join posts on posts.user_id = users.id
                       where commnets.created_at between '2023-01-01' and '2023-01-31'
                       order by comments.created_at desc
                       limit 1) = comments.id;

The table structures are quite simple.

users -> id, name

posts -> id, title, user_id, created_at, updated_at

comments -> id, comment, post_id, user_id, created_at, updated_at

FYI, I don't want to manipulate the raw query. I want to convert it into Laravel Eloquent. Since I know, Laravel uses whereColumn behind the JoinClause::on() method. so, I just want to figure out how can we write an eloquent for this.

Looking forward to your answers, Artisans.

Answer 1

I could make it happen with the below query:

    $commentsQuery = Comment::query();
    $postsQuery = Post::query();
    $usersQuery = User::query();

    $lastCommentsSubQuery = $commentsQuery->clone();
    $lastCommentsSubQuery->join($usersQuery->getModel()->getTable(), $usersQuery->qualifyColumn('id'), $commentsQuery->qualifyColumn('user_id'));
    $lastCommentsSubQuery->join($postsQuery->getModel()->getTable(), $postsQuery->qualifyColumn('user_id'), $usersQuery->qualifyColumn('id'));
    $lastCommentsSubQuery->whereBetween($commentsQuery->qualifyColumn('created_at'), [
        '2023-01-01',
        '2023-01-31',
    ]);
    $lastCommentsSubQuery->orderBy($commentsQuery->qualifyColumn('created_at'), 'DESC');
    $lastCommentsSubQuery->limit(1);

    $usersQuery->leftJoin($commentsQuery->getModel()->getTable() . ' as last_user_comments', function (JoinClause $joinClause) use ($lastCommentsSubQuery) {
        $joinClause->where($lastCommentsSubQuery, '=', new Expression('last_user_comments.id'));
    });
    $usersQuery->select([
        $usersQuery->qualifyColumn('id'),
        DB::raw('last_user_comments.created_at')
    ]);

    $users = $usersQuery->get();