Question 15668 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to use Laravel whereColumn with Subquery?

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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();

  2. 0

    I think this example can give you a brief idea,
    Create a query for your subquery,

    $latest_comments = Comment::select(DB::raw('post_id, MAX(created_at) as latest_comment_time'))
->groupBy('post_id')
->getQuery();

    And use it in main query

    $user_posts = User::leftJoin('posts', 'users.id', '=', 'posts.user_id')
->leftJoinSub($latest_comments, 'latest_comments', function ($join) {
    $join->on('posts.id', '=', 'latest_comments.post_id');
})
->leftJoin('comments', function ($join) {
    $join->on('posts.id', '=', 'comments.post_id')
        ->whereColumn('comments.created_at', '=', 'latest_comments.latest_comment_time');
})
->select('users.name', 'posts.title', 'comments.body as latest_comment')
->orderBy('posts.id')
->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search