Left join with last record of right table in Laravel & Mysql

November 15, 2017 sarpanch 2 Comments

Here we have 2 tables 'users' and 'answers' where users is left table and answers is right table which has user answers.

We wanted to left join users with answers but the join should be with the latest record or answers table.

$query = Users::select('users.id', 'users.user_name','answers.created_at as last_activity_date')
->leftJoin('answers', function($query) {
                    $query->on('users.id','=','answers.user_id')
                        ->whereRaw('answers.id IN (select MAX(a2.id) from answers as a2 join users as u2 on u2.id = a2.user_id group by u2.id)');
})
->where('users.role_type_id', Users::STUDENT_ROLE_TYPE)->get();

1

2

3

4

5

6

$query = Users::select('users.id', 'users.user_name','answers.created_at as last_activity_date')

->leftJoin('answers', function($query) {

$query->on('users.id','=','answers.user_id')

->whereRaw('answers.id IN (select MAX(a2.id) from answers as a2 join users as u2 on u2.id = a2.user_id group by u2.id)');

})

->where('users.role_type_id', Users::STUDENT_ROLE_TYPE)->get();

More from my site

2 thoughts on “Left join with last record of right table in Laravel & Mysql”

Amit

Thank you!
Working perfectly for me.

Reply
- December 6, 2018 at 12:32 pm
Sandeep

Excellent... It made my day... Got solution after lots of searching the answer for this particular problem on many portals.
Thanks buddy...

Reply
- April 28, 2020 at 7:20 pm

Leave a Reply Cancel reply