上下文
我有三个表，Users、Subreddits和Keywords。其思想是users可以监视多个subreddits，而subreddits可以监视多个子keywords。
由于一个用户可以监视多个子reddit，并且一个子reddit可以有多个用户监视它，所以我希望Users和Subreddits之间有多对多的关系。
同样，由于一个子reddit可以监视多个关键字，并且一个关键字可以由多个子reddit监视，因此我希望Subreddits和Keywords之间存在多对多关系。
我使用的是association object，而不是2个association tables，因为我有3个表要相互关联，而关联表仅用于2个表之间的关系。
实现
以下是我如何实现每个表和关联对象：
用户
class User(db.Model, JsonSerializer):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(128), index=True, unique=True)
password_hash = db.Column(db.String(128))
phone_num = db.Column(db.String(64), index=True, unique=True)
received_posts = db.Column(db.String(128), index=True, unique=True)
monitor = db.relationship('Monitor', back_populates='user')
//...
子版块
class Subreddit(db.Model, JsonSerializer):
__tablename__ = 'subreddits'
id = db.Column(db.Integer, primary_key=True)
subreddit_name = db.Column(db.String(128), index=True)
// ...
关键词
class Keyword(db.Model, JsonSerializer):
__tablename__ = 'keywords'
id = db.Column(db.Integer, primary_key=True)
keyword = db.Column(db.String(128), index=True)
monitor = db.relationship('Monitor', back_populates='keyword')
// ...
关联对象：
class Monitor(db.Model):
__table_name__ = 'monitors'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
subreddit_id = db.Column(db.Integer, db.ForeignKey("subreddits.id"), nullable=True)
keyword_id = db.Column(db.Integer, db.ForeignKey("keywords.id"), nullable=True)
user = db.relationship('User', back_populates='monitor')
subreddit = db.relationship('Subreddit')
keyword = db.relationship('Keyword', back_populates='monitor')
我正在尝试做什么
理想情况下，每次user跟踪一个subreddit和对应的多个keywords时，所有这些信息都存储在monitor对象中。
例如，
user1监视subreddit1，后者跟踪keyword1和keyword2- 所有这些关系都应存储在
monitor1中
这将导致：
- monitor1.user=
user1 - monitor1.subreddit=
subreddit1 - monitor1.keyword=
keyword1, keyword2
我尝试了什么
方法#1
使用keywords的列表初始化monitor实例。
# Parse the incoming data.
incoming = request.get_json()
subreddit_name = incoming["subredditName"]
subreddit_keywords = incoming["subredditKeywords"]
logged_in_user_id = incoming["id"]
# Query the User.
user = User.query.get(logged_in_user_id)
# Query the Subreddit.
subreddit = Subreddit.query.filter_by(subreddit_name=subreddit_name).first()
# Create a list of Keywords.
monitor_keywords = []
for kw in subreddit_keywords:
# check if Keyword objects are in the database already
if Keyword.query.filter_by(keyword=kw).first() is not None:
keyword = Keyword.query.filter_by(keyword=kw).first()
else: # create new Keyword objects
keyword = Keyword(kw)
monitor_keywords.append(keyword)
# Create the monitoring1 association object.
monitor1 = Monitor(user=user, subreddit=subreddit, keyword=monitor_keywords) <----- Problem
db.session.add(monitor1)
db.session.commit()
我无法用keyword=monitor_keywords初始化monitor1。
我收到以下错误：CCD_ 30。Stack Overflow上的其他提交与我的特定错误案例无关。
方法#2到#5
我尝试先初始化一个monitor对象，然后向其附加多个keywords，但这也不起作用。
# Query the User
user = User.query.get(logged_in_user_id)
# Create the association object
monitor = Monitor(user=user, subreddit=None, keyword=None)
db.session.add(monitor)
// ...
for kw in subreddit_keywords:
# check if Keyword objects are in the database already
if Keyword.query.filter_by(keyword=kw).first() is not None:
keyword = Keyword.query.filter_by(keyword=kw).first()
else: # create new Keyword objects
keyword = Keyword(kw)
# Initialize monitor.keyword so we can append to it.
if (monitor.keyword is None):
monitor.keyword = keyword
else: #All failed attempts to append to monitor.keyword.
# monitor.keyword.append(keyword) <- Method #2
# keyword.append(monitor) <- Method #3
# monitor.append(keyword) <- Method #4
# user.monitor.append(keyword) <- Method #5
问题
当我初始化一个关联对象时，我需要用多个keywords来初始化它。
无论是用keywords初始化关联对象，还是稍后用keywords更新关联对象，都不起作用。理想情况下，我想坚持方法#1的方法，但我对其他建议持开放态度。
我对您的要求感到困惑，但我认为在这种情况下，您的意思可能是让用户在特定的子reddit中侦听特定的关键字？这意味着关联对象将跟踪每个对象中的一个。为了使复数变得容易，也许Monitor。对于列外键，用单数名称命名它们是有意义的。与具有复数形式的backref的复数形式或单数形式的关系相反。
class Monitor(db.Model):
__table_name__ = 'monitors'
id = db.Column(db.Integer, primary_key=True)
# Singular
user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
# Singular
subreddit_id = db.Column(db.Integer, db.ForeignKey("subreddits.id"), nullable=True)
# Singular
keyword_id = db.Column(db.Integer, db.ForeignKey("keywords.id"), nullable=True)
# Backref is all monitors for user across ALL subreddits and keywords.
user = db.relationship('User', backref='monitors')
# Backref is all monitors for subreddit across ALL users and keywords.
subreddit = db.relationship('Subreddit', backref='monitors')
# Backref is all monitors for keyword across ALL users and subreddits.
keyword = db.relationship('Keyword', backref='monitors')
# Parse the incoming data.
incoming = request.get_json()
subreddit_name = incoming["subredditName"]
subreddit_keywords = incoming["subredditKeywords"]
logged_in_user_id = incoming["id"]
# Query the User.
user = User.query.get(logged_in_user_id)
# Query the Subreddit.
subreddit = Subreddit.query.filter_by(subreddit_name=subreddit_name).first()
# Create a list of Keywords.
for kw in subreddit_keywords:
# Create Keyword in database (this is another problem if you want the names to be unique)
keyword = Keyword(keyword=kw)
# The subreddit and keyword this user is watching.
db.session.add(Monitor(user=user, subreddit=subreddit, keyword=keyword))
db.session.commit()
# All subreddit/keyword pairs being watched by this user.
monitors = db.query(Monitor).filter(Monitor.user == user).all()
# All keywords being watched for this subreddit for this user
monitored_kws = db.query(Keyword).join(Keyword.monitors).filter(Monitor.user == user, Monitor.subreddit == subreddit).all()