Laravel Relationship where foreign key is two columns

Question

I have an application where I have some data in a table called "games" that looks something like this:

# games
+----+---------+--------+--------+
| id | name    | dataId | parent |
+----+---------+--------+--------+
| 1  | Team1   | 444    | null   |
+----+---------+--------+--------+
| 2  | Team2   | 445    | null   |
+----+---------+--------+--------+
| 3  | Team1.1 | 445    | 1      |
+----+---------+--------+--------+
| 4  | Team2.1 | 445    | 2      |
+----+---------+--------+--------+
| 5  | Team2.2 | 445    | 2      |
+----+---------+--------+--------+
| 6  | Team3   | 446    | null   |
+----+---------+--------+--------+
| 7  | Team4   | 447    | null   |
+----+---------+--------+--------+

As you can see a record can have a parent that points to the main record in the table.

I also have another table called "fixtures", this table looks as follows:

# fixtures
+----+--------+--------+-------+
| id | homeId | awayId | score |
+----+--------+--------+-------+
| 1  | 444    | 445    | 0     |
+----+--------+--------+-------+
| 2  | 445    | 446    | 0     |
+----+--------+--------+-------+
| 3  | 446    | 447    | 0     |
+----+--------+--------+-------+

So what I would like to do is use Eloquent to join the two tables as follows:

// For Home:
WHERE fixtures.homeId = games.dataId AND games.parent = null

// And another for Away:
WHERE fixtures.awayId = games.dataId AND games.parent = null

I was thinking I could use a OneToMany but I can only specify one foreign key.

Is this possible in Laravel? If so how?

Answer 1

Use this query for first one and change field name for second query

$result = DB::table('fixtures')
        ->join('games', 'fixtures.homeId', '=', 'games.dataId')
        ->select(DB::raw('fixtures.id','fixtures.homeId','fixtures.awayId','games.name','games.id'))
        ->where('games.parent', null)
        ->get();
return($result);

Answer 2

What you can try is returning an instance of BelongsToMany that has the right attributes set. As the original function belongsToMany function also returns an object of this class.

edit: some googling gave me this answer (How I can put composite keys in models in Laravel 5?) that has an answer using a Trait. Code can be found on github.