How to query SQLAlchemy to return a summed value of related records?

Question

I am creating a small program that keeps track of what tasks users have performed. Each task has a value. If a user has performed a task, a new entry is made in a third table (called Stripe) that saves the user and the performed task.

My Models look like this:

class User(db.Model):
    __tablename__ = 'user'
    # ...
    id = db.Column(db.Integer, primary_key=True)
    stripes = db.relationship(Stripe, backref=db.backref('related_user'), lazy='select')

class Task(db.Model):
    __tablename__ = 'task'
    # ...
    id = db.Column(db.Integer, primary_key=True)
    value = db.Column(db.Integer, nullable=False)
    stripes = db.relationship(Stripe, backref=db.backref('related_task'), lazy='select')


class Stripe(db.Model):
    __tablename__ = 'stripe'
    # ...
    id = db.Column(db.Integer, primary_key=True)
    task = db.Column(db.Integer, db.ForeignKey('task.id'), nullable=False)
    user = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

I have a problem to get the total (sum) of all value of all tasks a user has performed. Expected output:

User ID	Total stripe value	Additional user Columns
User_1	3	...
User_2	10	...
User_3	0	...

I've tried:

• Joining User and Stripe

       users = db.session.query(User).join(Stripe).all()
  

but this does not return any users that do not have stripes. I want it to return 0 then.

• querying stripes though user.

   users = User.query.all()
       for user in users:
           print(user.stripes) # returns list with stripes
           print(user.stripes.task) # returns error 
  

Last statement returns AttributeError: 'InstrumentedList' object has no attribute 'task'. Why can't I access task in this way?

How can I query my models so that it returns the user table with an additional column Total Stripes Value? (the total value of all performed tasks for that user)

Answer 1

You need to use outer join to include rows that can't join because their is no matching record (ie. no stripes). Join from users to stripes to tasks so that you can get the task value. Use func.sum to add the values up. Use func.coalesce to convert NULL to 0 for unmatched users, ie. users with no stripes. You can find LEFT OUTER JOIN, SUM and COALESCE in the documentation of whichever underlying database you are using.

q = session.query(User, func.coalesce(func.sum(Task.value), 0)).outerjoin(User.stripes).outerjoin(Stripe.related_task).group_by(User.id)
for user, value_total in q.all():
    print(user, value)

In order to reference the tasks from user you need to go through the stripes and use related_task, based on your backref, like this:

tasks = [stripe.related_task for stripe in user.stripes]