Condition only on latest record in hasMany relationship in laravel

Question

I have two table users and users_logs .

The users_logs table is linked with users by user_id.

The users_logs table may have multiple entries against different users, like 100 records may have against one user_id.

I want to fetch all users from users table with only one latest record of each users in users_logs table, then want to check a condition on the latest records of each user.

i have also created a hasMany relation in users table.

public function users_logs_details(){
    return $this->hasMany('App\UsersLog', 'user_id', 'id');
}

The code i tried is

$users = User::with(['users_logs_details'])
            ->whereHas('users_logs_details', function($query) {
                return $query->where('status', '=', 1)->latest()->take(1);
            })->get();

But i am not getting desired result because, this query is checking all records in users_logs table, but i want to check only the latest record.If status=1 is valid for any record of a user in users_logs table then it is returning the record, but i want to check status=1 only in the latest record, if status=0 in latest record (irrespective whether it is 1 in old record) it should return no result for that record. Please help, i would be highly thankful.

Note : images are for references only

Answer 1

A sub query join can be your solution. Sub query join will select only the latest event log, join it with the users and filter it by the status you need. To avoid making user field ambiguous, select only the user fields.

User::leftJoinSub(
    UsersLog::whereColumn('users_logs.user_id', 'users.id')
        ->latest()
        ->limit(1),
    'users_logs',
    'users_logs.user_id',
    'users.id'
)->where('users_logs.status', 1)
->select('users.*')
->get();