Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sqlalchemy join with sum and count of grouped rows

Hi i am working on a little prediction game in flask with 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 Bet 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 the full classes but it should do it for the question. A user can gather points for predicting the match outcome and gets different amount of points for predicting the exact outcome, the winner or the difference. I now want to have a list of the top users, where i have to sum up the points which i'm doing via

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 quite good, now there maybe the case that two players have the same sum of points. In this case the amount of correct predictions (rewarded with 3 points) would define the winner. I can get this list by

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

They both work well, but I think there has to be a way to get both querys together and get a table with username, points and "hitcount". I've done that before in SQL but i am not that familiar with SQLAlchemy and thought knots in my brain. How can I get both queries in one?

like image 371
bknux Avatar asked Jan 21 '14 23:01

bknux


Video Answer


1 Answers

In the query for tophits just replace the COUNT/filter_by construct with equivalent SUM(CASE(..)) without filter so that the WHERE clause for both is the same. The code below should do it:

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 a group_by clause to use the column which is in SELECT, as some database engines might complain otherwise. But you do not need to do it.

like image 150
van Avatar answered Oct 19 '22 09:10

van