SQLAlchemy ordering by count on a many to many relationship

by

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.

More Related Contents:

  1. flask-sqlalchemy or sqlalchemy
  2. How to execute raw SQL in Flask-SQLAlchemy app
  3. jsonify a SQLAlchemy result set in Flask [duplicate]
  4. How to build a flask application around an already existing database?
  5. SQLAlchemy ManyToMany secondary table with additional fields
  6. Flask sqlalchemy many-to-many insert data
  7. How to delete a record by id in Flask-SQLAlchemy
  8. Get inserted key before commit session
  9. Querying with function on Flask-SQLAlchemy model gives BaseQuery object is not callable error
  10. AttributeError: ‘int’ object has no attribute ‘_sa_instance_state’
  11. Getting first row from sqlalchemy
  12. Flask-SQLAlchemy Constructor
  13. flask sqlalchemy query with keyword as variable
  14. SQLAlchemy ORM conversion to pandas DataFrame
  15. Flask-SQLAlchemy import/context issue
  16. How to update SQLAlchemy row entry?
  17. SQL Alchemy ORM returning a single column, how to avoid common post processing
  18. Serve image stored in SQLAlchemy LargeBinary column
  19. Bulk insert with SQLAlchemy ORM
  20. Can SQLAlchemy be used with Google Cloud SQL?
  21. How can I verify Column data types in the SQLAlchemy ORM?
  22. Updates to JSON field don’t persist to DB
  23. ImportError: No module named MySQLdb
  24. SQLAlchemy: engine, connection and session difference
  25. SQLAlchemy – performing a bulk upsert (if exists, update, else insert) in postgresql
  26. SQLAlchemy: how to filter date field?
  27. How to update sqlalchemy orm object by a python dict
  28. Which SQLAlchemy column type should be used for binary data?
  29. Retrieve query results as dict in SQLAlchemy
  30. SQLALchemy dynamic filter_by

Leave a Comment