SQLAlchemy ordering by count on a many to many relationship

Question

This is a simplified example of my current models (I'm using the Flask SQLAlchemy extension):

like = db.Table(
    'like',
    db.Column('uid', db.Integer, db.ForeignKey('users.id')),
    db.Column('pid', db.Integer, db.ForeignKey('posts.id'))
)

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key = True)
    username = db.Column(db.String(20))

class Post(db.Model):
    __tablename__ = 'posts'

    id = db.Column(db.Integer, primary_key = True)
    title = db.Column(db.String(255))

    likes = db.relationship(
        'User',
        secondary = like,
        backref = db.backref('likes', lazy = 'dynamic'),
        lazy = 'dynamic'
    )

I'm trying to order Post's by the amount of likes it has.

This is the query I'm basically trying to issue:

SELECT p.*, COUNT(l.`pid`) as `likes`
FROM `posts` as p
LEFT JOIN `like` as l
    ON p.`id` = l.`pid`
GROUP BY p.`id`
ORDER BY `likes` DESC

I just haven't been able to get anything working on the SQLAlchemy side of things.

Thanks for any help anyone can offer.

score 57 · Accepted Answer · edited Aug 09 '22 at 08:39

57

I haven't used SQLAlchemy much so I figured I'd give it a shot. I didn't try to use your models, I just wrote some new ones (similar enough though):

likes = db.Table('likes',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('post_id', db.Integer, db.ForeignKey('post.id'))
)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20))

    def __repr__(self):
        return "<User('%s')>" % self.username

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(255))

    likes = db.relationship('User', secondary = likes,
        backref = db.backref('posts', lazy='dynamic'))

    def __repr__(self):
        return "<Post('%s')>" % self.title

You want to join the likes table, use func.count to count likes, group_by Post and then use order_by:

db.session.query(Post, func.count(likes.c.user_id).label('total'))\
 .join(likes).group_by(Post).order_by('total DESC')

I found the ORM tutorial and the rest of the SQLAlchemy documentation very useful.

edited Aug 09 '22 at 08:39

Ahmad

8,811
11
76
141

answered May 12 '11 at 08:33

Zach Kelling

52,505
13
109
108

Hey, thanks for the answer. Few things to note though. You have an extra parenthesis at the end of the query. MySQL didn't like the `'-total'` _(`'total DESC'` works fine though)_. Also I think it may be better to just group on the id so `.group_by(Post.id)`. Lastly I had an issue with `func.count(likes)`. It was translating to an empty count _(`COUNT()`)_. Supplying a column fixed it though `func.count(likes.c.did)`. Thanks again. – anomareh May 12 '11 at 09:06
@anomareh Great, glad it helps. I'll update my answer so it's (hopefully) more generically useful. I wonder if differing SQLAlchemy versions mattered here? I was using 0.7b4. – Zach Kelling May 12 '11 at 09:18
3

I had to add this .order_by(`text('count DESC')`) – Tushar Nitave Nov 09 '22 at 02:09

SQLAlchemy ordering by count on a many to many relationship

1 Answers1

Linked