SQLAlchemy ordering by count on a many to many relationship
13,577
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.
Author by
anomareh
Updated on June 17, 2022Comments
-
anomareh almost 2 years
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.
-
anomareh almost 13 yearsHey, 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 withfunc.count(likes)
. It was translating to an empty count (COUNT()
). Supplying a column fixed it thoughfunc.count(likes.c.did)
. Thanks again. -
zeekay almost 13 years@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.
-
Juggernaut almost 7 yearsyou sir are a lifesaver.