CODEWITHSUNDEEP
pythonsqlalchemy
Sonny
Sonny

Reputation: 43

Erratic sort order for hybrid_property sum expression in Flask-Admin using SQLAlchemy

Given the following models, I would like to add a sortable column to Flask Admin that shows all upcoming payments for an Organisation:

organisation_payments = db.Table(
    'organisation_payments',
    db.Column('payment_id', MyUUID(), db.ForeignKey('payment.id')),
    db.Column('organisation_id', MyUUID(), db.ForeignKey('organisation.id'))
)

class Organisation(db.Model):
    id = Column(ModelUUID(), primary_key=True)

    @hybrid_property
    def upcoming_payments(self):
        payments = Payment.query.filter(
            Payment.organisations.any(id=self.id),
            Payment.status == 'active'
        ).all()

        return sum([payment.amount for payment in payments])

    @upcoming_payments.expression
    def upcoming_payments(cls):
        return select([
            func.sum(Payment.amount)
        ]).where(and_(
            organisation_payments.c.organisation_id == cls.id,
            Payment.status == 'active'
        )).label('upcoming_payments')

class Payment(db.Model):
    id = Column(ModelUUID(), primary_key=True)
    amount = db.Column(db.Integer())
    status = db.Column(db.Unicode(255), default='active')
    organisations = db.relationship(
        'Organisation',
        secondary=organisation_payments,
        backref=db.backref('payments', lazy='dynamic')
    )

Note that a Payment could theoretically be mapped to multiple Organisations, hence the many-to-many relationship.

I have added upcoming_payments to column_sortable_list in a Flask-Admin ModelView, but when sorting on that column the results are erratic: https://i.sstatic.net/VxrAE.png

This is the most minimal version of the code, but I've also tried:

  1. using coalesce to force 0 for rows with no upcoming payments
  2. using as_scalar() in place of .label()
  3. Payment.organisations.any(id=cls.id) in place of organisation_payments.c.organisation_id == cls.id (this produced even more confusing and inconsistent results, and sorting asc/desc made no difference)

The values returned from the regular hybrid_property are correct, as is the result if I run this in the shell:

stmt = select([
        func.sum(Payment.amount)
    ]).where(and_(
        Payment.organisations.any(id=org.id),
        Payment.status == 'active',
    ))

res = db.engine.execute(stmt)
res.fetchone() 
(4036200L,)

However the result is wildly inaccurate if I run this:

stmt = select([
        func.sum(Payment.amount)
    ]).where(and_(
        organisation_payments.c.organisation_id == org.id,
        Payment.status == 'active',
    ))

res = db.engine.execute(stmt)
res.fetchone() 
(1204440000L,)

But neither the any() or association table method returns the correct sort order in Flask-Admin. What am I doing wrong here? I feel like I must be missing a distinct or a subquery, but I'm stumped.

UPDATE: All thanks to Ilja Everilä for guiding me to the answer:

    @upcoming_payments.expression
    def upcoming_payments(cls):
        q = select([coalesce(func.sum(Payment.amount), 0)])
        q = q.where(and_(
            organisation_payments.c.charity_id == cls.id,
            Payment.status == 'active',
            Payment.deleted.isnot(True)
        ))

        j = Payment.__table__.join(organisation_payments)
        q = q.select_from(j)

        return q.label('upcoming_donations')

Upvotes: 1

Views: 335

Answers (1)

Sonny
Sonny

Reputation: 43

UPDATE: All thanks to Ilja Everilä for guiding me to the answer:

    @upcoming_payments.expression
    def upcoming_payments(cls):
        q = select([coalesce(func.sum(Payment.amount), 0)])
        q = q.where(and_(
            organisation_payments.c.charity_id == cls.id,
            Payment.status == 'active',
            Payment.deleted.isnot(True)
        ))

        j = Payment.__table__.join(organisation_payments)
        q = q.select_from(j)

        return q.label('upcoming_donations')

Upvotes: 2

Related Questions