Flask SQLAlchemy - How to order query result by count of ForeignKey variable

Question

I'm implementing blog posts type Flask app using Flask-SQLAlchemy.

I'm trying to order the blog posts based on total number of likes each blog post received. I've tried using SQLALchemy hybrid property with query session.query(Post).order_by(Post.likes_count.desc()).all() which is similar to solution to a question asked earlier here

It throws error AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Post.likes has an attribute 'count'

Is there a way to get posts order using likes count which needs to be fed to paginate function.

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20))
    email = db.Column(db.String(120))
    likes = db.relationship('Like', backref='user',lazy='dynamic',foreign_keys='Like.user_id')
    def __repr__(self):
        return f"User('{self.username}', '{self.email}')"

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    date_posted = db.Column(db.DateTime, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    likes = db.relationship('Like', backref='post', lazy='dynamic')

    @hybrid_property
    def likes_count(self):
        return self.likes.count()

    def __repr__(self):
        return f"Post('{self.title}', '{self.date_posted}')"

class Like(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    post_id = db.Column(db.Integer, db.ForeignKey('post.id'))

This question is similar to How to order by count of many-to-many relationship in SQLAlchemy? However I was not able to get query results with len or count() method mentioned in answer/comments.

score 5 · Accepted Answer · answered Jul 02 '19 at 18:59

5

The way I would do it is by using sqlalchemy's func functionality, which just mirrors a SQL function. In this case, that would most likely be the following:

from sqlalchemy import func

session.query(Post).join(Like).group_by(Post.id).order_by(func.count().desc()).all()

This query results in something like the following raw SQL (although I just tried to make it a little cleaner by not writing everything out):

SELECT post.*
FROM post
JOIN like ON like.post_id = post.id
GROUP BY post.id
ORDER BY count() desc

Hope this helps!

answered Jul 02 '19 at 18:59

Ruben Helsloot

12,582
6
26
49

1

This worked. However the result wasn't containing rows which has 0 likes. So, I had to use outerjoin instead of join to get result rows with 0 likes. `session.query(Post).outerjoin(Like).group_by(Post.id).order_by(func.count().desc()).all()` – L.fole Jul 03 '19 at 04:17
1

Good solution! Happy to help – Ruben Helsloot Jul 03 '19 at 07:11
I had to read w3school sql tutorial to understand your above expression. I was wondering, if it's possible to order the results based on function result combination of likes and time it created. This is similar to reddit hotness of a post ranking. i.e., if I add ``` def hotness(self): return (self.likes.count() / (datetime.now() -self.date_created).days) ``` – L.fole Jul 03 '19 at 07:24
Yes, you can chain order by expressions, where it will use the second column to break ties in the first column. Something like `ORDER BY count() desc, date_posted` in SQL or `.order_by(func.count().desc(), Post.date_posted)` in SQLAlchemy. Is this what you're looking for? – Ruben Helsloot Jul 03 '19 at 07:27
Actually I'm looking for order the post query result by `hotness = round((like+1)/ (post_age_in_days)**(1/2),2)` where hotness defined as property of Post class – L.fole Jul 03 '19 at 07:32
1

I would define hotness as a hybrid_property, I'll append it to my answer to make it more readable – Ruben Helsloot Jul 03 '19 at 08:14
Sure. That would be really great. :) – L.fole Jul 03 '19 at 08:16

Flask SQLAlchemy - How to order query result by count of ForeignKey variable

1 Answers1