Flask SqlAlchemy many-to-many relationship only returns one result when accessed by relationship name
I have quite a few relationships using sql Alchemy like this:
file_favorites = db.Table('file_favorites',
db.Column('file_id', db.Integer(), db.ForeignKey('file.id')),
db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
db.Column('created_at', db.DateTime(), default=db.func.now()))
class File(db.Model, helpers.ModelMixin):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.Unicode, nullable=False)
description = db.Column(db.Unicode, nullable=False)
created_at = db.Column(db.DateTime(), default=func.now())
last_updated = db.Column(db.DateTime(), default=func.now(), onupdate=func.now())
user_id = db.Column('user_id', db.Integer(), db.ForeignKey('user.id'), nullable=False, index=True)
user = db.relationship('User')
favorited_by = db.relationship('User', secondary=file_favorites, lazy='dynamic')
def is_favorite_of(self, user):
query = File.query
query = query.join(file_favorites)
query = query.filter(file_favorites.c.file_id == self.id)
query = query.filter(file_favorites.c.user_id == user.id)
return query.count() > 0
def favorite(self, user):
if not self.is_favorite_of(user):
self.favorited_by.append(user)
def unfavorite(self, user):
if self.is_favorite_of(user):
self.favorited_by.remove(user)
I expect that accessing the favorited_by property will result in a query that tries to return a list of users who prefer this file. However, it looks like the request only has access to the FIRST user in order to access this file. I am puzzled by this and expect that I misunderstand the sqlalchemy relationship. Here is the result I am experiencing:
def create_model(model_class, *args, **kwargs):
model = model_class(*args, **kwargs)
db.session.add(model)
db.session.commit()
return model
def test_favorited_by(self):
user = create_model(User, username='user', email='user@user.net', password='password')
user1 = create_model(User, username='user1', email='user1@user.net', password='password')
user2 = create_model(User, username='user2', email='user2@user.net', password='password')
file = create_model(File, name='file', description='a description', user=user)
file.favorite(user1)
file.favorite(user)
file.favorite(user2)
db.session.commit()
print file.favorited_by
results in this request:
SELECT "user".id AS user_id, "user".email AS user_email, "user".username AS user_username, "user".password AS user_password, "user".active AS user_active, "user".last_login_at AS user_last_login_at, "user".current_login_at AS user_current_login_at, "user".last_login_ip AS user_last_login_ip, "user".current_login_ip AS user_current_login_ip, "user".login_count AS user_login_count, "user".last_updated AS user_last_updated, "user".created_at AS user_created_at
FROM "user", file_favorites
WHERE :param_1 = file_favorites.file_id AND "user".id = file_favorites.user_id
What user1 ultimately returns, if the order is switched, the first user to select the file will always be returned to the user.
source to share
Your problem is with is_favorite_of
. It does not include user
in the check. You need to add another filter.
def is_favorite_of(self, user):
query = File.query
query = query.join(file_favorites)
query = query.filter(file_favorites.c.file_id == self.id)
query = query.filter(file_favorites.c.user_id == user.id)
return query.count() > 0
Alternatively, this whole function can be simplified to:
def is_favorite_of(self, user):
return user in self.favorited_by
source to share