Sqlalchemy Union_all And All() Returning Incorrect Number Of Items

For some reason when I'm using SQLAlchemy's union_all and .all(), it's returning the incorrect number of items. As you can see below, I broke each one down to see where the error w

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   


classAbstractNotification(AbstractConcreteBase, db.Model):
    __table__ = NoneclassNotificationBaseModel:
    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_attrdefnotifier_id(cls):
        return db.Column(db.Integer, db.ForeignKey('user.id'))

    @declared_attrdefnotified_id(cls):
        return db.Column(db.Integer, db.ForeignKey('user.id'))


classPostNotification(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)


classCommentNotification(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)