I have three tables, Users, Subreddits, and Keywords. The idea is that users can monitor multiple subreddits, and subreddits can monitor multiple keywords.
Since one user can monitor multiple subreddits, and one subreddit can have multiple users monitoring it, I would like to have a many-to-many relationship between Users and Subreddits.
On the same note, since one subreddit can monitor multiple keywords, and one keyword can be monitored by multiple subreddits, I would like to have a many-to-many relationship between Subreddits and Keywords.
I am using an association object as opposed to 2 association tables, since I have 3 tables that I want to relate with each other, while an association table is only for relationships between 2 tables.
The following is how I implemented each table, and the association object:
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')
Ideally, every time a user tracks a subreddit and corresponding multiple keywords, all this information is stored into a monitor object.
For example,
user1 monitors subreddit1, which is keeping track of keyword1 and keyword2.monitor1.This should result in:
user1subreddit1keyword1, keyword2Initializing a monitor instance with a list of keywords.
# 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()
I cannot initialize monitor1 with keyword=monitor_keywords.
I receive the following error:
AttributeError: 'list' object has no attribute '_sa_instance_state'.
Other submissions on Stack Overflow aren't relevant to my particular error case.
I've tried initializing a monitor object first, then appending multiple keywords to it but this also did not work.
# 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
When I initialize an association object, I need to initialize it with multiple keywords.
Whether it's initializing the association object with the keywords, or later updating the association object with the keywords, it does not work. Ideally I'd like to stick with Method #1's approach, but I am open to other suggestions.
I am confused about your requirements but I think in this case it seems that maybe you mean for a user to listen for a specific keyword in a specific subreddit? Which would mean that the association object would track one of each. To make the plural easy maybe Monitor. For column foreign keys it makes sense to name them with singular names. In contrast to relationships which will either be plural or singular with a backref that is plural.
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()