Eloquent query all the latest records for each id

Question

I'm looking to do something like this, but with Eloquent: get latest record for each ID

Personally, I have three tables: Game, Assignments, Users.

Games get assigned to Users through the Assignments table. However, when a game needs to be assigned to a new user, I just make a new Assignment, and go off of the latest assignment for each game.

My end goal is to be able to grab a collection of games that any given User has assigned to them. However, to do that, I first need to be able to query Assignments and filter out any assignment that isn't the most recent for that given Game id.

Any thoughts? I've been trying some things (see below) but not getting anywhere.

Game function (works):

public function latestAssignment() {
  return $this->hasOne('App\Models\Game_Assignment', 'game_id')->latest();
}

Game_Assignment function:

public function isLatestAssignment() {
  if($this->game->latestAssignment()->id == $this->id) {
    return true;
  } else {
    return false;
  }
}

User function (throws error):

public function current_game_assignments() {
  return $this->hasMany('App\Models\Game_Assignment', 'statistician_id')->where('isLatestAssignment', true);
}

Let me know what y'all think I can do!

Can a game have only one user assigned at a time? In other words, would your data have the same meaning if there were no previous game assignments for a game once the user changed? _(Not trying to delete old assignments, just trying to understand your logic!)_ — Namoshek, Sep 11 '19 at 10:56
Yes, a game can only have one user assigned at a time. However, the data wouldn't have the same meaning. On the admin end, I want to be able to track how often a user has to have a game reassigned away from them (or to them) — Hunter, Sep 11 '19 at 11:03
Absolutely, throwing away data isn't the way to go in most scenarios. Just needed this bit of information. — Namoshek, Sep 11 '19 at 11:06

Namoshek · Accepted Answer · 2019-09-11T12:11:43.297

What you can do is select your games with the id of the latest assigned user in a subquery. You can then use a special relation that utilizes this subquery column to join to the users table:

class Game extends Model
{
    public function latestUser()
    {
        return $this->hasOne(User::class, 'id', 'latest_user_id');
    }
}


$games = Game::query()
    ->select('*') // necessary to avoid overrides by selectSub()
    ->selectSub(
        Assignment::query()
            ->whereColumn('game_assignments.game_id', 'games.id') // table prevents ambiguity
            ->latest()
            ->select('game_assignments.user_id')
            ->take(1),
        'latest_user_id'
    )
    ->with('latestUser')
    ->get();

After re-reading your question, I come to a different solution. If you want all the games for a specific user of which the user is the latest assigned user, you can use the following query. It uses a little hack with the wrapping, but without this it doesn't allow to filter on the subquery:

// only for demonstration purposes
$user  = User::find(1);

$games = Game::query()
    ->fromSub(
        Game::query()
            ->select('*') // necessary to avoid overrides by selectSub()
            ->selectSub(
                Assignment::query()
                    ->whereColumn('game_assignments.game_id', 'games.id')
                    ->latest()
                    ->select('game_assignments.user_id')
                    ->take(1),
                'latest_user_id'
            ),
        'games'
    )
    ->where('latest_user_id', $user->id)
    ->get();

Please note that the subquery alias (second argument) must be the table name of your games.

I had to take the comma off after whereColumn (error) but when I implement this, it returns ALL games on the system, not just ones for that statistician. Not sure why either... — Hunter, Sep 11 '19 at 11:55
Alright, added another query after re-reading the question. Sorry about the comma btw, must have overlooked it. — Namoshek, Sep 11 '19 at 12:12

Eloquent query all the latest records for each id

1 Answers1