Laravel过滤器基于具有一种关系 [英] Laravel filter based on has one relationship

问题描述

我正在拔头发，我觉得我已经尝试了所有方法！

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

这是我的问题.

我有2张桌子

用户

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

状态

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

从上表中可以看到，每个用户可以有许多状态"，但是每个用户必须具有1个最新状态，我正在这样做(请告诉我是否做错了)

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

然后在我的视图中打开一个窗体，该窗体通过$request将过滤器传递给控制器​​.

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

我需要能够使用过滤器，并将其应用到1个最新状态.例如，如果某人搜索日期2018-06-04 09:00:00和用户ID 1，则我需要它显示NO RESULTS，因为该用户的最近1条记录与该日期不匹配，但是目前，它将如果不匹配，则跳过最近的一个，并获得下一个匹配的记录.

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

哪一行获取了正确的最新行，但是如果我尝试使用status.status, 'out'，它将跳过并获得状态为2的记录.

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.

我也尝试过这样

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

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

return $users;

这很好用，但是在尝试为过滤器附加任何GET参数时，分页会中断.

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

普通话

我需要能够获取用户的最新状态，然后一旦获得该状态，就需要在其中应用语句/过滤器/参数的地方，如果不匹配，则完全忽略该用户.

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.

推荐答案

您必须将JOIN与子查询结合起来:

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

这篇关于Laravel过滤器基于具有一种关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！