CODEWITHSUNDEEP
laraveleloquent
Hunter
Hunter

Reputation: 458

Eloquent query all the latest records for each id

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!

Upvotes: 0

Views: 92

Answers (1)

Namoshek
Namoshek

Reputation: 6544

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.

Upvotes: 1

Related Questions