How can I do multiple "order_by" in Flask-SQLAlchemy?

Question

Let's say I have a User model with fields popularity and date_created. I want to do the following query:

SELECT * FROM user ORDER BY popularity DESC, date_created DESC LIMIT 10

In SQLAlchemy, for a single one this works:

User.query.order_by(User.popularity.desc()).limit(10).all()

Should I just add another order_by()? Or put both popularity and date_created in my current order_by()?

I want popularity to have priority on date_created for ordering.

Solution 1

This should work

User.query.order_by(User.popularity.desc(), User.date_created.desc()).limit(10).all()

Solution 2

What you also could do:

from sqlalchemy import and_, or_
User.query.order_by(and_(User.popularity.desc(), User.date_created.desc())).all()

Note: and_ and or_ are coming from the sqlalchemy library and not from flask_sqlalchemy library. sqlalchemy is a dependency for flask_sqlalchemy, so you should be good.

LTS: You can mix sqlalchemy lib with flask_alchemy lib

Solution 3

You can also use sqlalchemy.sql.text like this:

from sqlalchemy.sql import text

User.query.order_by(text("popularity desc, date_created desc")).limit(10).all()