'sqlalchemy update relationship with primaryjoin when object in other relationship is edited to not meet condition
I have 2 relationships in the User class. One that contains all of the User's Bets (User.bets) and one that contains all of the User's Bets that don't have a winner (winner == 0) (User.active_bets). If I set the winner to 1 on an active bet using User.bets it doesn't update the active_bets relationship until session is closed/flushed. Is there any way I can link the 2 so when I make changes to a Bet from user.bets it checks if it matches the condition of primaryjoin on user.active_bets and removes it if winner != 0. I am also using SQL 2.0 with expire_on_commit = False
Code:
User Class:
class User():
__tablename__ = "user"
code = Column(String(8), primary_key=True)
active_bets = relationship("Bet", primaryjoin="and_(Bet.user_id == User.code, Bet.winner == 0)", overlaps="active_bets, user", cascade="all, delete")
bets = relationship("Bet", back_populates="user", cascade="all, delete", overlaps="active_bets, user")
Bet Class:
@mapper_registry.mapped
class Bet():
__tablename__ = "bet"
code = Column(String(8), primary_key = True, nullable=False)
winner = Column(Integer, nullable=False)
user_id = Column(Integer, ForeignKey("user.code"), nullable=False)
user = relationship("User", back_populates="bets")
Test:
with Session.begin() as session:
user = get_all_db("User", session)[0] #just gets the first user in table
#print(user.active_bets) if user.active_bets is called all user.active_bets under will be []
user.bets[-1].winner = 0
user.bets[-2].winner = 0
user.bets[-3].winner = 0
bet = user.bets[-4]
bet.winner = 0
print(user.active_bets) #prints 4 bets
bet.winner = 1
print(bet.winner) #prints 1
print(user.active_bets) #prints 4 bets
with Session.begin() as session:
user = get_all_db("User", session)[0]
print(user.active_bets) #prints 3 bets
I currently have a fix that reloads the session but I assume there is a better way.
My Solution:
with Session.begin() as session:
user = get_all_db("User", session)[0] #just gets the first user in table
#print(user.active_bets) if user.active_bets is called all user.active_bets under will be []
user.bets[-1].winner = 0
user.bets[-2].winner = 0
user.bets[-3].winner = 0
bet = user.bets[-4]
bet.winner = 0
print(user.active_bets) #prints 4 bets
bet.winner = 1
print(bet.winner) #prints 1
session.flush([user.bets[-4]])
session.refresh(user)
print(user.active_bets) #prints 3 bets
with Session.begin() as session:
user = get_all_db("User", session)[0]
print(user.active_bets) #prints 3 bets
Is there a property of the relationship that checks if the the conditions are the same or something that automatically expires the relationship if the winner has been changed.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
