Sqlalchemy join with sum and number of grouped rows

Question

Hi, I am working on a small predictive flask game with a flask-sqlalchemy I have a user model:

class User(db.Model, UserMixin): id = db.Column(db.Integer, primary_key=True) nick = db.Column(db.String(255), unique=True) bets = relationship('Bet', backref=backref("user")) 

and my betting model

 class Bet(db.Model): id = db.Column(db.Integer, primary_key=True) uid = db.Column(db.Integer, db.ForeignKey('user.id')) matchid = db.Column(db.Integer, db.ForeignKey('match.id')) points = db.Column(db.Integer) 

Both are not complete classes, but should do this for the question. The user can collect points to predict the result of the match and get a different number of points to predict the exact result, winner or difference. Now I want to have a list of the best users, where I have to take stock, which I do through

 toplist = db.session.query(User.nick, func.sum(Bet.points)).\ join(User.bets).group_by(Bet.uid).order_by(func.sum(Bet.points).desc()).all() 

This works well, it is now possible that two players have the same amount of points. In this case, the number of correct predictions (received 3 points) will determine the winner. I can get this list

 tophits = db.session.query(User.nick, func.count(Bet.points)).\ join(User.bets).filter_by(points=3).all() 

Both of them work well, but I think there should be a way to get both queries together and get a table with username, dots and "hitcount". I did this before in SQL, but I am not familiar with SQLAlchemy and was thinking about the nodes in my brain. How can I get both requests in one?

Answer 1

In the tophits query tophits simply replace the COUNT/filter_by equivalent SUM(CASE(..)) without filter , so that the WHERE same for both. The code below should do this:

 total_points = func.sum(Bet.points).label("total_points") total_hits = func.sum(case(value=Bet.points, whens={3: 1}, else_=0)).label("total_hits") q = (session.query( User.nick, total_points, total_hits, ) .join(User.bets) .group_by(User.nick) .order_by(total_points.desc()) .order_by(total_hits.desc()) ) 

---

Note that I changed the group_by clause to use a column that is in SELECT, as some database engines may complain differently. But you do not need to do this.