Flask-SQLAlchemy Sum of a Column of a Relationship

Question

I do have 2 classes like these =>

class User(db.Model):
    __tablename__ = "user"
    user_id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(32), unique=True, nullable=False)
    password = db.Column(db.String(77), unique=False, nullable=False)
    server_limit = db.Column(db.Integer, unique=False, nullable=False, server_default="4")
    servers = db.relationship('Server', backref='owner', lazy='dynamic')

class Server(db.Model):
    __tablename__ = "server"
    server_id = db.Column(db.Integer, primary_key=True)
    server_admin = db.Column(db.Integer, db.ForeignKey("user.user_id"))
    server_port = db.Column(db.Integer, unique=False, nullable=False)
    server_slot = db.Column(db.Integer, unique=False, nullable=False, server_default="32")

Now Im trying to get sum of server_slot column where for example user_id is 1.

I know there is questions with accepted answer about this but the difference is Im trying to do it with servers ( db.relationship ) that I assigned in my User class.

I did it with an alternative method that I created for User class =>

def used(self):
    return db.session.execute("SELECT SUM(server.server_slot) FROM server WHERE server_admin={}".format(self.user_id)).scalar()

How can I do it using db.session.query() ? Im looking for something that I can get it from db.session.query(User).all()

I dont want to use db.session.query(db.func.sum(Server.server_slot)).filter_by(server_admin=self.user_id).scalar() Cause Im passing a list to my Flask page, The list is made by db.session.query(User).all() so I can iterate over it using a for loop inside my Jinja2 Template and show each user information in a list like this =>

{% for user in users %}
    <td>user.username</td>
    <td>user.server_limit</td>
    <td>...</td>
    <td>user.used_slots()</td>
{% endfor %}

I can use user.servers.value("server_slot") but it returns only first server's server_slot, I also tried to iterate over user.servers.all() so I could sum their server_slot inside a nested loop, but I can't assign variables any value inside of a loop and get it outside the loop.

Let me know if my question is not clear enough (Cause I know it might be).

Answer 1

Now I believe it can't be done in the way I wanted.

Answer 2

Define a hybrid property/expression on your User model.

A simple self-contained example (I've simplified your models):

import random
from select import select
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func
from sqlalchemy.ext.hybrid import hybrid_property

app = Flask(__name__)

# Create in-memory database
app.config['DATABASE_FILE'] = 'sample_db.sqlite'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + app.config['DATABASE_FILE']
db = SQLAlchemy(app)


class User(db.Model):
    __tablename__ = "user"
    user_id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(32), unique=True, nullable=False)
    servers = db.relationship('Server', backref='owner', lazy='dynamic')

    @hybrid_property
    def server_slot_count(self):
        return sum(server.server_slot for server in self.servers)

    @server_slot_count.expression
    def server_slot_count(cls):
        return (
            select([func.sum(Server.server_slot)]).
            where(Server.server_admin == cls.user_id).
            label('server_slot_count')
        )


class Server(db.Model):
    __tablename__ = "server"
    server_id = db.Column(db.Integer, primary_key=True)
    server_admin = db.Column(db.Integer, db.ForeignKey("user.user_id"))
    server_slot = db.Column(db.Integer, unique=False, nullable=False, server_default="32")


@app.route('/')
def index():
    html = []
    for user in User.query.all():
        html.append('User :{user}; Server Count:{count}'.format(user=user.username, count=user.server_slot_count))

    return '<br>'.join(html)


def build_sample_db():
    db.drop_all()
    db.create_all()

    for username in ['DarkSuniuM', 'pjcunningham']:

        user = User(
            username=username,
        )
        db.session.add(user)
        db.session.commit()
        for slot in random.sample(range(1, 100), 5):
            server = Server(
                server_admin=user.user_id,
                server_slot=slot
            )
            db.session.add(server)

        db.session.commit()


if __name__ == '__main__':
    build_sample_db()
    app.run(port=5000, debug=True)

Your User model now has a property server_slot_count.

{% for user in users %}
    <td>user.username</td>
    <td>user.server_limit</td>
    <td>...</td>
    <td>user.server_slot_count</td>
{% endfor %}