SQLAlchemy:如果连接返回多个结果，则仅显示最新结果 [英] SQLAlchemy: show only latest result if a join returns multiple results

问题描述

我正在尝试创建一个 Flask 应用程序，以显示单个玩家的最新分数.所以一个玩家可以有多个分数，但在排行榜上我只想显示她最近的分数.

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.

我的models.py:

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)

在我的 app.py 中，我有以下内容:

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)

在我的(简化的)模板 home.html 中:

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.

我需要在查询中做什么才能确保只显示每个玩家的最新分数?我尝试添加 .(Score.timestamp == db.session.query(func.max(Score.timestamp))) 但这使得唯一的结果是最新分数，而不是最新分数播放器.

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.

推荐答案

您可以使用 DISTINCT ON ... ORDER BY Postgresql 中的惯用语"以获取greatest-n-per-group，其中 n 等于 1，假设您的表不是巨大:

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())

由于 Score.timestamp 不可为空，因此您无需担心降序和 NULL.

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

这篇关于SQLAlchemy:如果连接返回多个结果，则仅显示最新结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！