laravel Laravel中具有两个外键字段的数据库一对多

Database One-to-Many with two foreign key fields in Laravel

提问by Noel De Martin

I have been trying to define some Database schema to use the laravel framework. I want to model a Football match. The first step I wanted to do is to define the Entity Relationship diagram, but I found this (which I thought would be quite trivial) to be confusing in some aspects.

我一直在尝试定义一些数据库模式来使用 laravel 框架。我想模拟一场足球比赛。我想做的第一步是定义实体关系图，但我发现这（我认为这很微不足道）在某些方面令人困惑。

First, the obvious approach is to say that a Match is related with two Teams, and a Team is related to any number of Matches. So, we would have a "Many to Many" relationship.

首先，显而易见的方法是说一场比赛与两个团队相关，而一个团队与任意数量的比赛相关。所以，我们会有一个“多对多”的关系。

But the implementation of a many to many relation is to have two tables and an intermediate table to relate both entities. I think this would be too much, when I know that a Match will always have two Teams and simply having two columns (local_id and visitant_id) with foreign keys to the Teams table would be enough. Plus, I want to be able to do:

但是多对多关系的实现是有两个表和一个中间表来关联两个实体。我认为这太过分了，因为我知道一场比赛总是有两个团队，并且只有两列（local_id 和visitant_id）带有 Teams 表的外键就足够了。另外，我希望能够做到：

Match::find(1)->local() or Match::find(1)->visitant();

So, thinking on this I am implementing a "One to Many" relation, but with this I have another issue. To retrieve all the matches a Team has played I would like to do:

因此，考虑到这一点，我正在实施“一对多”关系，但对此我还有另一个问题。要检索团队参加的所有比赛，我想做：

Team::find(1)->matches(); 

But I cannot do this because I can only specify one key column when defining the matches() method in eloquent (by default it would be team_id, but it should be visitant_id and local_id).

但是我不能这样做，因为在eloquent中定义matches()方法时我只能指定一个关键列（默认情况下它会是team_id，但它应该是visitant_id和local_id）。

回答by Noel De Martin

After some more digging into the source code I found there is a way to actually keep my database schema as it is and achieve what I want (at least in Laravel 4). I posted my problem in github and Taylor Otwell (creator of the framework) gave me the correct answer: https://github.com/laravel/framework/issues/1272

在深入研究源代码后，我发现有一种方法可以实际保持我的数据库架构原样并实现我想要的（至少在 Laravel 4 中）。我在 github 上发布了我的问题，Taylor Otwell（框架的创建者）给了我正确的答案：https: //github.com/laravel/framework/issues/1272

Quoting him, it should be as easy as this:

引用他的话，应该就是这么简单：

class Team extends Eloquent  {
    public function allMatches()
    {
        return $this->hasMany('Match', 'visitant_id')->orWhere('local_id', $this->id);
    }
}

And then...

进而...

$team = Team::find(2);
$matches = $team->allMatches;

Update:The github link is not working because laravel doesn't take bug reports in that way any more: http://laravel-news.com/2014/09/laravel-removes-github-issues/

更新：github 链接不起作用，因为 laravel 不再以这种方式报告错误：http://laravel-news.com/2014/09/laravel-removes-github-issues/

回答by vFragosop

This is one of those famous database design problems. Friendship relationships, for instance, suffer from that same difficulty. Since you are using Eloquent, I would suggest you to stick with many to many approach and have an extra boolean column localon your intermediate table

这是那些著名的数据库设计问题之一。例如，友谊关系也面临同样的困难。由于您使用的是 Eloquent，我建议您坚持使用多对多的方法，并local在中间表上添加一个额外的布尔列

class Match extends Eloquent {
    public $includes = array('team'); // Always eager load teams
    public function teams() {
        return $this->has_many_and_belongs_to('team')->with('local');
    }
    public function get_local() {
        foreach ($this->teams as $team) {
            if ($team->pivot->local) return $team;
        }
    }
    public function get_visitant()   {
        foreach ($this->teams as $team) {
            if (!$team->pivot->local) return $team;
        }
    }
}

class Team extends Eloquent  {
    public function matches() {
        return $this->has_many_and_belongs_to('match')->with('local');
    }
    // I'm doing separate queries here because a team may have
    // hundreds of matches and it's not worth looping through
    // all of them to retrieve the local ones
    public function matches_as_local()  {
        return $this->has_many_and_belongs_to('match')->with('local')
            ->where('pivot_local', '=', 1);
    }
    public function matches_as_visitant()  {
        return $this->has_many_and_belongs_to('match')->with('local')
            ->where('pivot_local', '=', 0);
    }
}

Obs:

观察：

The method has_many_and_belongs_to(...)->with('field')has nothing to do with eager loading. It tells Eloquent to load the intermediate table column fieldand put that in the pivot.

该方法has_many_and_belongs_to(...)->with('field')与预先加载无关。它告诉 Eloquent 加载中间表列field并将其放入数据透视表中。

Usage:

用法：

$match = Match::find(1);

$match->local; // returns local team
$match->visitant; // returns visitant team

$team = Team::find(1);
$team->matches; // returns all matches
$team->matches_as_local; // ...
$team->matches_as_visitant; // ...

foreach ($team->matches as $match) {
    if ($match->pivot->local) {
        // put nice local icon here
    } else {
        // put nice visitant icon here
    }
}