Sqlalchemy overwrite field from association table

user_2102843 0
Viewed 271

I have two entities, Player and Game and the relationship between them is many to many. Both Player and the connection between Player and Game (PlayerGame) have a score

class Player(db.Model):
 id = db.Column(db.Integer, primary_key=True, autoincrement=True)
 score = db.Column(db.Float)
 name = db.Column(db.String(45), nullable=False)

class Game(db.Model):
  id = db.Column(db.Integer, primary_key=True, autoincrement=True)
  name = db.Column(db.String(45))

class PlayerGame(db.Model):
  player_id = db.Column(db.Integer, db.ForeignKey('players.id'), primary_key=True)
  game_id = db.Column(db.Integer, db.ForeignKey('games.id'), primary_key=True)
  score = db.Column(db.Float)

  player = db.relationship("Player", backref="games")
  game = db.relationship("Game", backref="players")

I want to write a query using sqlalchemy that given a game id, returns all related players, but their score will be overwritten by score of the game

So far my query looks like this

Player.query.join(PlayerGame, and_(
    PlayerGame.player_id == Player.id,
    PlayerGame.game_id == game_id))\
.options(contains_eager(Player.games))

I was thinking about changing Player.score and PlayerGame.score label names, or exluding Player.score but none of the above really seemed to work

It is possible to do it after the query in python code, but ideally, I want it to be part of the query

flask-sqlalchemy sqlalchemy orm python
1 Answers

I have adjusted your model a little bit, and then below used a query that is not delivering back a "Player" object because the result is an attribute from the player and an attribute from PlayerGame:

class Player(db.Model):
  id = db.Column(db.Integer, primary_key=True, autoincrement=True)
  score = db.Column(db.Float)
  name = db.Column(db.String(45), nullable=False)
  playergames = db.relationship('PlayerGame', backref='player', lazy=True, 
     cascade="delete")

class Game(db.Model):
  id = db.Column(db.Integer, primary_key=True, autoincrement=True)
  name = db.Column(db.String(45))
  playergames = db.relationship('PlayerGame', backref='game', lazy=True, 
  cascade="delete")

class PlayerGame(db.Model):
  player_id = db.Column(db.Integer, db.ForeignKey('player.id'), primary_key=True)
  game_id = db.Column(db.Integer, db.ForeignKey('game.id'), primary_key=True)
  score = db.Column(db.Float)

p = Player(name = "Hoffmann", score = 7.5)
p1 = Player(name = "Fink", score = 4.5)
db.session.add(p)
db.session.add(p1)

g = Game(name = "VfB")
g1 = Game(name = "HSV")

db.session.add(g)
db.session.add(g1)

pg = PlayerGame(player = p, game = g, score = 1.5)
pg1 = PlayerGame(player = p1, game = g, score = 3.5)
db.session.add(pg)
db.session.add(pg1)
db.session.commit()    

playergamescores = db.session.query(Player.name, 
    PlayerGame.score).join(Player).filter(PlayerGame.game_id == g.id).all()   
print(playergamescores)

Output is then [('Hoffmann', 1.5), ('Fink', 3.5)]

user_120102050
Related