Flask-SQLAlchemy - Order shows by number of followers
flask, python, sqlalchemy
Solution
Working solution:
sub = db.session.query(favorite_series.c.series_id, func.count(favorite_series.c.user_id).label('count')).group_by(favorite_series.c.series_id).subquery()
shows = db.session.query(Serie, sub.c.count).outerjoin(sub, Serie.id == sub.c.series_id).order_by(db.desc('count')).all()
Problem
I am trying to create a query which will return all shows in database ordered by number of users who have it as favorite. Simplified working code: ``` from flask import Flask from flask.ext.sqlalchemy import SQLAlchemy from sqlalchemy.sql import func import logging app = Flask(__name__) db = SQLAlchemy(app) logging.basicConfig() logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) favorite_series = db.Table('favorite_series', db.Column('user_id', db.Integer, db.ForeignKey('users.id')), db.Column('series_id', db.Integer, db.ForeignKey('series.id'))) class User(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String) favorite_series = db.relationship('Serie', secondary=favorite_series, backref=db.backref('users', lazy='dynamic')) def __repr__(self): return '<User {0}>'.format(self.name) class Serie(db.Model): __tablename__ = 'series' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String) def __repr__(self): return '<Serie {0}>'.format(self.name) u1 = User() u1.name = 'user1' u2 = User() u2.name = 'user2' u3 = User() u3.name = 'user3' s1 = Serie() s1.name = 'Serie1' s2 = Serie() s2.name = 'Serie2' s3 = Serie() s3.name = 'Serie3' s4 = Serie() s4.name = 'Serie4' s5 = Serie() s5.name = 'Serie5' u1.favorite_series.extend([s1, s3, s5]) u2.favorite_series.extend([s1, ]) u3.favorite_series.extend([s1, s2, s3]) u1.favorite_series.extend([s1, s2]) db.session.add(u1) db.session.add(u2) db.session.add(u3) db.session.add(s1) db.session.add(s2) db.session.add(s3) db.session.add(s4) db.session.add(s5) db.create_all() db.session.commit() ``` And I try to retrieve them with: ``` shows = Serie.query.join(Serie.users).order_by(func.count(Serie.users)).all() print shows ``` But this throws error in SQL syntax, I tried to search for something but could not come up with anything working. Any help would be appreciated.