1

I am trying to create a relationship in Laravel that would provide me with the latest records only. I have three entities: Game, Player and Turn. I need to be able to create a relationship on Game to return currentTurns only.

This is my database:

Players
+--+--------+
|id|name    |
+--+--------+
|1 |John Doe|
|2 |Jane Doe|
|3 |John Roe|
+--+--------+

Turns
+--+-------+---------+----+-------------------+
|id|game_id|player_id|roll|created_at         |
+--+-------+---------+----+-------------------+
|1 |1      |1        |3   |2020-03-19 08:27:42|
|2 |1      |2        |5   |2020-03-19 08:27:46|
|3 |1      |3        |1   |2020-03-19 08:27:51|
|4 |1      |1        |6   |2020-03-19 08:28:05|
+--+-------+---------+----+-------------------+

Games
+--+--------+
|id|name    |
+--+--------+
|1 |Foobar  |
+--+--------+

Originally, I would write a raw SQL query somewhat like this:

SELECT * FROM turns WHERE game_id = 1 GROUP BY player_id ORDER BY created_at DESC

I found out that this behaviour has changed since MySQL 5.7. So I found an approach that works without having to disable the strict mode in Laravel.

My current raw query looks like this:

SELECT turns.* 
FROM turns
JOIN (
    SELECT player_id, max(created_at) as created_at FROM turns WHERE game_id = 1 GROUP BY player_id
) as latest_turns 
ON latest_turns.player_id = turns.player_id 
AND latest_turns.created_at = turns.created_at 
AND game_id = 1

I am not sure if this is the most performant way but I'm looking for ways to achieve this using Eloquent relationship method on Game class.

<?php

// ...

public function currentTurns(): HasMany
{
    return $this->turns(); // ???
}

Any help is welcome, thanks!

miken32
  • 42,008
  • 16
  • 111
  • 154
Jan Richter
  • 1,976
  • 4
  • 29
  • 49
  • Is it actually possible for a player to have two simultaneous game_ids? If not, then the final condition is redundant. Alternatively, assuming ids are chronological, then this would be the better comparison. So, either way, the final `AND game_id = 1` is redundant. – Strawberry Mar 19 '20 at 13:16
  • Yes, the `Player` is shared across games. Integer IDs are only temporary though, which means I cannot use `max()` for them. – Jan Richter Mar 19 '20 at 17:58
  • Can you edit your question to include what your current relationships are? Is `turns` acting as a pivot table? Have you created a model for it? – miken32 Mar 19 '20 at 19:23

1 Answers1

1

The joinSub() method lets you join a subquery, as you are doing in your SQL. Assuming you have a Game::turns() method set up, you can use it in an accessor method that joins a subquery into it:

class Game extends Model
{
    public function turns()
    {
        return $this->hasMany(Turn::class)->with('player');
    }

    public function getLatestTurnsAttribute()
    {
        $subquery = Turn::select('player_id')
            ->selectRaw('max(created_at) as created_at')
            ->where('game_id', $this->id)
            ->groupBy('player_id');

        return $this->turns()
            ->joinSub($subquery, 'latest_turns', function ($j) {
                $j->on('latest_turns.player_id', '=', 'turns.player_id')
                    ->on('latest_turns.created_at', '=', 'turns.created_at');
            })
            ->get();
    }
}

Now, Game::find(1)->latest_turns will create the following SQL, very similar to your original (note I filtered the subquery by the game ID as well, to reduce row counts.)

select * from `turns`
inner join (
    select `player_id`, max(created_at) as created_at from `turns` where `game_id` = 1 group by `player_id`
) as `latest_turns`
on `latest_turns`.`player_id` = `turns`.`player_id`
and `latest_turns`.`created_at` = `turns`.`created_at`
where `turns`.`game_id` = 1 and `turns`.`game_id` is not null

Live example is here.

miken32
  • 42,008
  • 16
  • 111
  • 154