You use a many-to-many relationship with an association object.
I think the models are okay, but I've restructured them a bit.
class Vote(db.Model):
__tablename__ = 'votes'
id = db.Column(db.Integer, primary_key=True)
# Foreign Keys
plan_id = db.Column(db.Integer, db.ForeignKey('plans.id'))
post_id = db.Column(db.Integer, db.ForeignKey('posts.id'))
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
# Relationships
# If a user, post or plan is deleted, referencing votes are also removed.
# The associated plans and posts are loaded with the vote using a JOIN statement.
plan = db.relationship('Plan',
backref=db.backref('votes', cascade='all, delete-orphan'),
lazy='joined')
post = db.relationship('Post',
backref=db.backref('votes', cascade='all, delete-orphan'),
lazy='joined')
user = db.relationship('User',
backref=db.backref('votes', cascade='all, delete-orphan'))
def __repr__(self):
return f'Vote(plan_id={self.plan_id}, post_id={self.post_id}, user_id={self.user_id})'
class Plan(db.Model):
__tablename__ = 'plans'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), index=True, nullable=False)
def __repr__(self):
return f'Plan(name={self.name})'
class Post(db.Model):
__tablename__ = 'posts'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(255), index=True, nullable=False)
def __repr__(self):
return f'Post(title={self.title})'
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), index=True, nullable=False)
# All plans and posts that have been voted for can be reached via jointable.
# CAUTION, objects can be added to the lists, but because of the viewonly flag
# they are not transferred to the database during a commit.
# An inconsistent state is therefore possible.
#
# _voted_plans = db.relationship(
# 'Plan',
# secondary='votes',
# backref=db.backref('users_voted', viewonly=True),
# viewonly=True
# )
#
# _voted_posts = db.relationship(
# 'Post',
# secondary='votes',
# backref=db.backref('users_voted', viewonly=True),
# viewonly=True
# )
def __repr__(self):
return f'User(name={self.name})'
On the one hand, you can use the ORM method using the virtual relationships to
list all the associated models. In this case, your table votes acts as a jointable
and the associated objects of the classes Post and Plan can be queried directly
via the relationship.
plan_post_pairs = [(vote.plan, vote.post) for vote in Vote.query.filter_by(user_id=user_id).all()]
As an alternative, you can also write your own request.
As an example I'll give you both a SQL SELECT statement and a JOIN statement.
I also ask for the identifier of the vote to list duplicate votes by a user on the same plan-post combinations.
# SELECT stmt
items = db.session.query( # SELECT ... FROM ...
Vote.id, Plan, Post
).filter( # WHERE ...
Vote.plan_id == Plan.id, # ... AND
Vote.post_id == Post.id, # ... AND
Vote.user_id == user_id # ...
).all()
# JOIN stmt
items = db.session.query(Vote.id, Plan, Post)\ # SELECT ...
.select_from(Vote)\ # FROM ...
.outerjoin(Plan, Post)\ # LEFT OUTER JOIN ... ON ...
.filter(Vote.user_id == user_id)\ # WHERE ...
.all()
The following example is a little more advanced and may help you in the future. All plan-post combinations of a user are requested including the number of votes for the respective pair from this user.
subquery = db.session\
.query(Vote.plan_id, Vote.post_id, db.func.count('*').label('count'))\
.group_by(Vote.plan_id, Vote.post_id)\
.filter(Vote.user_id == user_id)\
.subquery()
items = db.session\
.query(Plan, Post, subquery.c.count)\
.select_from(subquery)\
.outerjoin(Plan, subquery.c.plan_id == Plan.id)\
.outerjoin(Post, subquery.c.post_id == Post.id)\
.all()