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