使用join的Eloquent查询来检索数据透视表和相关数据

In my controller I have a query like this:

在我的控制器中,我有一个这样的查询:

$userinfos = User::with('skills');
$userinfos->with('positions');
$userinfos=$userinfos->get();

And that works fine, in my view, I get the position name like this:

这很好,在我看来,我得到这样的位置名称:

@forelse ($userinfos[0]->positions as $key=>$position)
    {{ $position->name}}
@empty
    No position listed
@endforelse

I have another like this to retrieve related company data:

我有另一个这样的方法来检索相关的公司数据:

$positions = \DB::table('positions')
->select(\DB::raw('position_user.id as id, positions.id as position_id, positions.name as position_name, companies.id as company_id, companies.name as company_name, position_user.user_id'))
->join('position_user','position_user.position_id','=','positions.id')
->join('companies','companies.id','=','position_user.company_id')
->join('users','users.id','=','position_user.user_id')
->where('position_user.user_id',$userid)
->get();

And that also works fine to get the positions and company name:

这也适用于获得职位和公司名称:

@forelse($positions as $key=>$position)
    {{$position->position_name}} @ {{$position->company_name}}
@empty
    No position listed
@endforelse

I am sure there must be a way to combine the two so that I can get the company name in the first query. I am looking for something like this:

我确信必须有一种方法将两者结合起来,以便我可以在第一个查询中获取公司名称。我正在寻找这样的东西:

$userinfos = User::with('skills');
$userinfos->with('positions');
$userinfos->whereHas('positions', function($thisQuery) 
{
    $thisQuery->join('companies','companies.id','=','position_user.company_id');
});

$userinfos=$userinfos->get();

Is that even possible? Any ideas/direction would be greatly appreciated!

这有可能吗?任何想法/方向将不胜感激!

EDIT:

Should I perhaps be looking at adding in a join to my Position class to enable the above:

我是否应该考虑在我的Position类中添加一个连接以启用上述功能:

public function users()
{
    return $this->belongsToMany('App\User')->withTimestamps()
    ->withPivot('company_id');
    ->join('companies','companies.id','=','position_user.company_id');
}

If so - how would I access that in the view?

如果是这样 - 我将如何在视图中访问它?

1 个解决方案

#1

You should probably create your own pivot model such as UserPosition, and override the newPivot() method.

您应该创建自己的枢轴模型,例如UserPosition,并覆盖newPivot()方法。

class User extends Eloquent {

public function positions() { 
   return $this->belongsToMany('Position')->withPivot('company_id');
}

public function newPivot(Model $parent, array $attributes, $table, $exists) {

if ($parent instanceof Position) {
    return new UserPosition($parent, $attributes, $table, $exists);
}

    return parent::newPivot($parent, $attributes, $table, $exists);
}

}

And you'd have the pivot model such as

而且你有像这样的枢轴模型

class UserPosition extends  Illuminate\Database\Eloquent\Relations\Pivot { 

    public function company() {
        return $this->belongsTo('Company');
    }
}

Then you can do something like the code below. Of course you could iterate positions instead of fetching first().

然后你可以做类似下面的代码。当然你可以迭代位置而不是取第一个()。

$user = User::find(1);
$user->positions()->first()->pivot->company

Please keep in mind I just wrote these as a guideline. Didn't test any of it. Hopefully you can apply the logic.

请记住,我只是将这些作为指南。没有测试任何一个。希望您可以应用逻辑。

#1