A Relationship Of Two Foreign Keys To The Same Table

It is not effective, however, I would approach this by using belongs to many relationship.

Such as

'Team belongs to many MatchUps' 'MatchUps belongs to many Team'

This way, you can do like this for example,

if (Team::find(1)->matchups()->id == Team::find(2)->matchups()->id), then these teams are having same matchup.

also you can do reversely.

Honestly, this is the only thing that I can come up with right now,

and I am sure that there are much better ways.

@dongyukang Thanks for the reply!

Team::find(1)->matchups will not work. Matchup table have 'team1_id' and 'team2_id' columns. Our relationship will not know which column to look to find our team.

if (Team::find(1)->matchups->id == Team::find(2)->matchups->id) will not work also. Because a team may have many other matchups. So ->matchups relationship may return an array of ID's, even if we could possible wrote a 'matchups' relationship :(

What I meant is you rewrite the table structure.

Since I suggested belongsToMany relationship, you create a pivot table

and take team_id and match_up id.

And in Team table, you will only have just id, so does your Matchup table.

And a team should be matched up with another team then,

you toggle team A to a certain match and toggle another team to certain match.

and doing this if (Team::find(1)->matchups->id == Team::find(2)->matchups->id),

you can achieve which teams are having what matchups.

Oh now I understand, thanks.

Every Matchup can only have 2 teams. If I create a pivot table, doesn't that mean more than 2 teams can possibly have a Matchup?

If my Matchup table has 2 columns for Team ID, this ensures every matchup will have only 2 teams.

I totally understood your perspective, but do you think creating a pivot table is the correct way for this problem, since it opens the gate for binding more than 2 teams to a Matchup?

You just create relationships for team1 and team2 telling it which key to use.

public function team1()
{
    return $this->belongsTo('App\Team','team1_id');
}

public function team2()
{
    return $this->belongsTo('App\Team','team2_id');
}

no need for a pivot table since a match can only belong to one team or the other team. Its not a 'many' situation.

@Snapey Lets say we have two teams with ID's 1 and 2. They are having a Matchup. The Matchup ID is 3.

In this case Matchup table becomes:

'id'-> 3
'team1_id' -> 1
'team2_id' -> 2

Assuming we have our first Team's ID, 1. How can we reach this Matchup? This ID can possibly be in team1_id column or team2_id column. How will eloquent know?

coming from which direction?

You may have seen an earlier version of my post. I fleshed it out with the relationships. Specifying the key tells eloquent which column contains ref to team 1 and which to team 2

@Snapey Yes I understand what you did there. That will allow me to find teams by Matchup ID. This is not a problem. The problem is finding the Matchup by having Team ID. What to write in Team Modal?

For Example:

public function matchups()
{
    return $this->hasMany('App\Matchup', 'team1_id'); // or team2_id
}

Won't work.

Think about this like we have no information about the Matchup, but we have one of the teams ID in our hand. We need to find the Matchup. Eloquent won't know which column to look for this Team ID, it won't know if 'team1_id' or 'team2_id' is the right one to look.

@Snapey

I came up with this solution for Team Modal:

public function matchups1()
{
    return $this->hasMany('App\Matchup', 'team1_id');
}
public function matchups2()
{
    return $this->hasMany('App\Matchup', 'team2_id');
}

If I call both of them, I can have the Matchup information by knowing Team ID. But is this the correct solution?

You could do a where team_1 = $team->id or where team_2=$team->id

but I would probably have two relationships. If you don't have the concept of something like 'hometeam' or 'awayteam' then probably just use matched1 and matched2. It means two queries though.

Team model

public function matched1()
{
    return $this->hasMany(Matchup::class,'team1_id');
}

public function matched2()
{
    return $this->hasMany(Matchup::class,'team2_id');
}

public function otherTeam()
{
    if($this->matched1->id == $this->id {
        return $this->matched1;

    }
    return $this->matched2;
}
    

and then you should be able to;

$matchups = $team->load('matched1','matched2');


//view

@foreach($matchups as $matchup)

    {{ $matchup->otherTeam()->name; }}

@endforeach 

edit: we are thinking along the same lines

@Snapey Thank you so much! I marked your comment as accepted.

But one last question, if you let me.

Regarding to your last comment, If I call matched1 and matched2 relations together, one of them will return the Matchup, an instance of Matchup Modal, right?

But then you wrote that in View I can use

@foreach($matchups as $matchup)

    {{ $matchup->otherTeam()->name; }}

@endforeach 

Isn't otherTeam relation a method of Team Modal? How can I call it from a Matchup Instance? It confused me. What is the purpose of otherTeam method?

Thanks again.