Flask-SQLAlchemy - Order shows by number of followers

Question

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.

This might be a duplicate of http://stackoverflow.com/questions/7954696/elixir-sqlalchemy-order-by-count-of-onetomany-relationship — madjar, May 31 '12 at 10:59
It's different because I use ManyToMany, I use cross table and that example has foreign key. — TrueFurby, Jun 01 '12 at 08:38
Final 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()` — TrueFurby, Jun 02 '12 at 18:50

score 2 · Accepted Answer · edited Jan 17 '13 at 20:27

2

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()

edited Jan 17 '13 at 20:27

plaes

31,788
11
91
89

answered Nov 21 '12 at 11:15

TrueFurby

457
4
19

Flask-SQLAlchemy - Order shows by number of followers

1 Answers1