使用Laravel的ORM Eloquent通过多个数据透透表表查询对象

What I want to do, is to retrieve data through multiple relationships (pivot tables). I have three database tables

我想要做的是通过多个关系（数据透视表）检索数据。我有三个数据库表

users, cities, and jobs.

用户，城市和工作。

They are built like this (They aren't, just to give you a glimpse)

他们是这样建造的（他们不是，只是为了让你一瞥）

Users table
id int(11) PK, Autoincrement,
name varchar(255)

用户表id int（11）PK，Autoincrement，name varchar（255）

Cities table
id int(11) PK, Autoincrement,
name varchar(255)

Cities table id int（11）PK，Autoincrement，name varchar（255）

Jobs table
id int(11) PK, Autoincrement,
name varchar(255)

作业表id int（11）PK，Autoincrement，name varchar（255）

Now I have pivot tables, because these are many to many relationships. I got the tables city_user and job_user.

现在我有数据透视表，因为它们是多对多的关系。我得到了表city_user和job_user。

CityUser table
city_id int(11),
user_id int(11)

CityUser表city_id int（11），user_id int（11）

JobUser table
job_id int(11),
user_id int(11)

JobUser表job_id int（11），user_id int（11）

In every Class (User, City, Job), I got the relationships defined with a belongsToMany method.

在每个类（用户，城市，工作）中，我获得了使用belongsToMany方法定义的关系。

City class

城市班

/**
 * Defines the relation between the city and its users
 *
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function users()
{
    return $this->belongsToMany(User::class, 'city_user');
}

User class

用户类

/**
 * Defines the relation between the user and its cities
 *
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function cities()
{
    return $this->belongsToMany(City::class, 'city_user');
}

The same goes for the jobs table. What I now want to do is to get all users that are in a specific city and have a specific job.

就业表也是如此。我现在想要做的是获得特定城市中的所有用户并拥有特定的工作。

Let's imagine I want to query all users who live in City with the ID 5, and have the job with the ID 10.

我们想象一下，我想查询所有居住在City的ID为5的用户，并拥有ID为10的作业。

Going for one relationshiop is fairly easy

争取一个关系是相当容易的

$users = City::find(5)->users;

$ users = City :: find（5） - > users;

要么

$users = Job::find(10)->users;.

$ users = Job :: find（10） - > users;。

But, how do I achieve that for multiple relationships in this case? I tried to do it like this

但是，在这种情况下，如何实现多种关系？我试着这样做

$users = City::find(10)->with(['users' => function($query) use ($jobId) {
    // Here I'm stuck. I wouldn't know how to query further? Maybe like this
    return $query->with('jobs')->whereJobId($jobId);
}]);

But, when I do it like this, I'm getting 0 results, so there must be something wrong. Any ideas?

但是，当我这样做的时候，我得到的结果是0，所以一定有什么不对的。有任何想法吗？

I'm getting an error though

我收到了一个错误

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'job_id' in 'where clause'' in

致命错误：未捕获异常'PDOException'，消息'SQLSTATE [42S22]：未找到列：1054'where子句'中的未知列'job_id'

I also tried it like this (inside the with(['consultants']))

我也是这样尝试的（在with（['顾问']内））

$query->with(['jobs' => function ($query) use ($jobId) {
    $query->whereJobId($jobId);
}]);

but that doesn't look right. Also I'm getting a Collection of Cities, but I need one of Users.

但这看起来不对。我也得到了一个城市集合，但我需要一个用户。

Then I tired it (temporary) like this

然后我就这样累了（暂时）

$users = User::with(['cities' => function ($query) use($cityId) {
    /** @var \Illuminate\Database\Eloquent\Relations\BelongsToMany $query */
    $query->wherePivot('city_id', $cityId);
}])->get();

But, then I'm getting ALL users, instead of just the ones, which is even more confusing, because the documentation says

但是，然后我得到所有用户，而不仅仅是那些用户，这更令人困惑，因为文档说

Sometimes you may wish to eager load a relationship, but also specify additional query constraints for the eager loading query. Here's an example:

有时您可能希望加载关系，但也为热切加载查询指定其他查询约束。这是一个例子：

$users = App\User::with(['posts' => function ($query) {
    $query->where('title', 'like', '%first%');
}])->get();

In this example, Eloquent will only eager load posts that if the post's title column contains the word first. Of course, you may call other query builder to further customize the eager loading operation:

在这个例子中，如果帖子的标题栏首先包含单词，Eloquent将只会加载帖子。当然，您可以调用其他查询构建器来进一步自定义预先加载操作：

But why doesn't it work then?

但为什么它不起作用呢？

1 个解决方案

#1

You can solve your fatal error this way:

您可以通过以下方式解决致命错误：

$users = City::find(10)->with(['users.jobs', function($query) use ($jobId) {
        $query->whereJobId($jobId);
    });
}]);

But this will not filter the users for a specific job ID. For example we have two users with IDs 1 and #2. User 1 is related to job 10 while user 2 is not. The code above will give you both users for $jobId = 10, because the users query will be executed first without any filters for jobs and the jobs query will be executed after the users query.

但是这不会过滤用户的特定作业ID。例如，我们有两个ID为1和＃2的用户。用户1与作业10相关，而用户2则不是。上面的代码将为你们两个用户提供$ jobId = 10，因为用户查询将首先执行而不会有任何作业过滤器，并且在用户查询后将执行作业查询。

You have to use the join() method of the query builder:

您必须使用查询构建器的join（）方法：

$users = User::join('job_user', 'users.id', '=', 'job_user.user_id')
    ->join('city_user', 'users.id', '=', 'city_user.user_id')
    ->where('job_user.job_id', $jobId)
    ->where('city_user.city_id', $cityId)
    ->get();

#1