How can I do multiple "order_by" in Flask-SQLAlchemy?
PostgresqlSqlalchemyFlaskFlask SqlalchemyPostgresql Problem Overview
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.
Postgresql Solutions
Solution 1 - Postgresql
This should work
User.query.order_by(User.popularity.desc(), User.date_created.desc()).limit(10).all()
Solution 2 - Postgresql
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 - Postgresql
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()
Solution 4 - Postgresql
I have another example:
user = User.query.order_by(User.id.desc()).limit(10).all()
s.t
User is my class db.Modle