Laravel雄辩的查询从另一个模型获得用户

I have another table called tableb and it has a user relationship defined through the user_id field.

我有另一个名为tableb的表，它有一个通过user_id字段定义的用户关系。

I want to run a query against tableb where a certain date is within a certain range but then I want to grab the user table associated with that row but I only want it to grab the user if it's not been grabbed yet. I'm trying to do this all in 1 DB query. I have most of it done, but I'm having trouble with the unique part of it.

我想要运行一个针对tableb的查询，其中某个日期在一定范围内，但是我想要获取与该行关联的用户表，但我只希望它在未被捕获的情况下获取用户。我试着在一个DB查询中做这些。我已经完成了大部分，但是我对它独特的部分感到困惑。

Here's what I have right now:

以下是我现在所拥有的:

    $tableB = TableB::select('users.*')
    ->join('users', 'tableb.user_id', '=', 'users.id')
    ->where('tableb.start_date', '>', date('Y-m-d'))
    ->get();

So right now I have 3 entries in tableB from the same user, and ideally I'd like to only get 1 entry for that user.

现在我有3个条目来自同一个用户，理想情况下，我只希望这个用户有1个条目。

How would I go about doing this?

我该怎么做呢?

3 个解决方案

#1

Since you're selecting only users data, just add a groupBy clause in your query.

由于只选择用户数据，所以只需在查询中添加groupBy子句。

$tableB = TableB::select('users.*')
    ->join('users', 'tableb.user_id', '=', 'users.id')
    ->where('tableb.start_date', '>', date('Y-m-d'))
    ->groupBy('users.id')
    ->get();

#2

You should just add groupBy like this :

你应该这样添加groupBy:

$tableB = TableB::select('users.*')
    ->join('users', 'tableb.user_id', '=', 'users.id')
    ->where('tableb.start_date', '>', date('Y-m-d'))
    ->groupBy('users.id')
    ->get

#3

Try This Code

试试这个代码

App/user.php

public function getrelation(){      
      return $this->hasMany('App\tableB', 'user_id');
}

In Your Controller

在你的控制器

Controller.php

use App/user;

public funtion filterByDate(user $user)
{
    $date = '2016-02-01';
    $result = $user->WhereHas('getrelation', function ($query) use($date) {
                                    $query->whereDate('tableb.start_date', '>', $date)
                                          ->first();
                        });    
}

#1