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).
1 Answers
Answers 1
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 %} 
0 comments:
Post a Comment