SQLAlchemy Union_all And All() Returning Incorrect Number Of Items
Solution 1:
The union u
is not polymorphic in the sense that it'd recognize which rows represent PostNotification
and which CommentNotification
entities – it simply treats all rows as representing the primary entity PostNotification
.
It also happens that you have 2 "identical" notifications in both tables, i.e. they have the same numeric value for primary key. SQLAlchemy deduplicates model entities based on primary key when querying, as noted here by the author of SQLAlchemy, and so len(u.all())
returns fewer results. u.count()
on the other hand counts in the database and so counts all rows. This deduplication does not take place if querying attributes or more than 1 entity.
Solution 2:
Looks like I figured it out. I can now query AbstractNotification
directly db.session.query(AbstractNotification).all()
from sqlalchemy.ext.declarative import AbstractConcreteBase
class AbstractNotification(AbstractConcreteBase, db.Model):
__table__ = None
class NotificationBaseModel:
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(150), nullable=False)
read = db.Column(db.Boolean, default=False)
created = db.Column(db.DateTime, index=True, default=datetime.utcnow)
@declared_attr
def notifier_id(cls):
return db.Column(db.Integer, db.ForeignKey('user.id'))
@declared_attr
def notified_id(cls):
return db.Column(db.Integer, db.ForeignKey('user.id'))
class PostNotification(AbstractNotification, NotificationBaseModel):
post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
comment_id = db.Column(db.Integer)
__mapper_args__ = {
'polymorphic_identity': 'post_notification',
'concrete': True
}
def __repr__(self):
return '<PostNotification {}>'.format(self.name)
class CommentNotification(AbstractNotification, NotificationBaseModel):
post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
comment_id = db.Column(db.Integer, db.ForeignKey('post_comment.id'))
__mapper_args__ = {
'polymorphic_identity': 'comment_notification',
'concrete': True
}
def __repr__(self):
return '<CommentNotification {}>'.format(self.name)
Post a Comment for "SQLAlchemy Union_all And All() Returning Incorrect Number Of Items"