Laravel filter based on has one relationship

Question

I am pulling my hair out over this one, and I feel like I have tried every method!

Here is my problem.

I have 2 tables

USERS

ID | FIRSTNAME   | EMAIL_ADDRESS
1  | Joe Bloggs  | [e-mail] 

STATUS

ID | USER_ID | STATUS | DATE
1  |  1      | 'In'   | 2018-06-04 09:01:00
2  |  1      | 'Out'  | 2018-06-04 09:00:00

As you can see by the tables above, each user can have many status', but each user has to have 1 most recent status, which I am doing like this (please tell me if I am doing it wrong)

public function statusCurrent(){
    return $this->hasOne('App\Status', 'user_id', 'id')->orderBy('date', 'desc')->limit(1);
}

I then a form on in my view, which passes filters to the controller via a $request.

I need to be able to use the filters, and apply them to the 1 most recent status. For example, if someone searches for the date 2018-06-04 09:00:00 and a user id 1, I need it to show NO RESULTS, because the 1 most recent record for that user does not match that date, but at the moment, it will just jump over the one most recent if it doesn't match, and get the next record that does.

I have tried what seems like every method, I have tried like this

$users = Users::with(['StatusCurrent' => function($query){
  $query->where('status.status', 'In');
}])->get();

Which gets the correct most recent row, but then if i try status.status, 'out' instead, it just jumps over and gets record number 2 where the status is out.

I've also tried like this

$users = Users::has('StatusCurrent')->paginate(10);

    if(!empty($request->statusIn)){
        $users = $users->filter(function ($item){
            $item = $item->statusCurrent->status == 'in'; 
            return $item;
        });
    }

return $users;

Which works great but then the pagination breaks when trying to append any GET parameters for the filters.

Plain English

I need to be able to get the most recent status for the user, then once I have it, I need to be able to apply where statements/filters/arguments to it, and if they don't match, completely ignore that user.

Answer 1

You can get the ID first, then do your query with filters:

$status_id = Users::find($user_id)->statusCurrent()->id;

Now do the actual query, using $status_id in whereHas clause:

$users = Users::with('statusCurrent')
    ->whereHas('statusCurrent', function($query) use($status_id) {
        $query->where('status.status', 'In')
              ->where('id',$status_id);
    })->get();

Answer 2

You have to combine a JOIN with a subquery:

$users = User::select('users.*')
    ->join('status', 'users.id', 'status.user_id')
    ->where('status.status', 'in')
    ->where('status.id', function($query) {
        $query->select('id')
            ->from('status')
            ->whereColumn('user_id', 'users.id')
            ->orderByDesc('date')
            ->limit(1);
    })
    ->get();