SQLAlchemy: show only latest result if a join returns multiple results

Question

I'm trying to create a Flask app that shows the latest score from individual players. So a player can have multiple scores, but on the leaderboard I only want to show her most recent score.

My models.py:

class Player(db.Model):
    __tablename__ = 'player'
    id = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String, nullable=False)
    score = db.relationship('Score', backref='player', lazy='dynamic')

    def __init__(self, firstname):
        self.firstname = firstname

    def __repr__(self):
        return '<id {}>'.format(self.id)


class Score(db.Model):
    __tablename__ = 'score'
    id = db.Column(db.Integer, primary_key=True)
    timestamp = db.Column(db.DateTime, nullable=False)
    score = db.Column(db.String, nullable=True)
    player_id = db.Column(db.Integer, db.ForeignKey('player.id'))

    def __init__(self, score, player_id):
        self.timestamp = datetime.now()
        self.score = score
        self.player_id = player_id

    def __repr__(self):
        return '<id {}>'.format(self.id)

In my app.py I have the following:

@app.route('/', methods=['GET', 'POST'])
@login_required
def home():
    """Render homepage"""

    players_with_score = db.session.query(
        Player, Score).join(Score)

    return render_template('home.html', players_with_score=players_with_score)

And in my (simplified) template home.html:

<tbody>
  {% for players_with_score in players_with_score %}
  <tr>
    <td>{{ players_with_score.Player.firstname }}</td>
    <td>{{ players_with_score.Score.score }}</td>
    <td>{{ players_with_score.Score.timestamp }}</td>
  </tr>
  {% endfor %}
</tbody>

This results in a proper table with all the scores, but now if a player has multiple scores, it will show that player also multiple times.

What do I need to do in the query to ensure only the latest score per player is shown? I tried adding .(Score.timestamp == db.session.query(func.max(Score.timestamp))) but that made that the only result was the latest score, not the latest score per player.

You want the [tag:greatest-n-per-group], and the answer depends greatly on what DB you are using. There are "generic" solutions using for example outer joins or scalar subqueries, but then there are also DB specific shortcuts etc. — Ilja Everilä, May 19 '17 at 12:41

score 4 · Accepted Answer · edited May 23 '17 at 12:34

You can use the DISTINCT ON ... ORDER BY "idiom" in Postgresql to get the greatest-n-per-group, where n equals 1, given that your table isn't huge:

players_with_score = db.session.query(Player, Score).\
    join(Score).\
    distinct(Player.id).\
    order_by(Player.id, Score.timestamp.desc())

Since Score.timestamp is not nullable, you do not need to worry about descending order and NULLs.

SQLAlchemy: show only latest result if a join returns multiple results

1 Answers1

Linked