88

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.

daaawx
  • 3,273
  • 2
  • 17
  • 16
Noé Malzieu
  • 2,530
  • 3
  • 22
  • 27

4 Answers4

142

This should work

User.query.order_by(User.popularity.desc(), User.date_created.desc()).limit(10).all()
Mark Mishyn
  • 3,921
  • 2
  • 28
  • 30
codegeek
  • 32,236
  • 12
  • 63
  • 63
  • Well the fact is that I tried `User.query.order_by(User.popularity.desc(),User.date_created.desc()).limit(10).all()` `User.query.order_by(User.popularity.desc()).order_by(User.date_created.desc()).limit(10).all()` `User.query.order_by(User.date_created.desc()).limit(10).all()` and they give me different results... So it seems that giving a second order_by neither overrides the first one nor adds the order condition to the first one? – Noé Malzieu Apr 03 '13 at 16:11
  • (and none of these queries fails, they all give me results, I just don't know which one of the first two is the one I want) – Noé Malzieu Apr 03 '13 at 16:15
  • 6
    Ok, actually both give me the same answer, and I found out the underlying SQL query (thanks to http://stackoverflow.com/questions/4617291/how-do-i-get-a-raw-compiled-sql-query-from-a-sqlalchemy-expression ) and they are indeed the same, and the right one. Sorry, and thank you so much for your answer ! – Noé Malzieu Apr 03 '13 at 16:19
  • 2
    For anyone wondering if there is a difference between `order_by(o1).order_by(o2)` and `order_by(o1, o2)`, there is not. In the first case I thought is it being overwritten by the second `order_by`, but no. It's working just as expected. – BcK May 06 '22 at 13:41
7

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()
okebinda
  • 291
  • 3
  • 4
  • 1
    From my testing, it seems that you need to sanitize what gets into `text()` if you get it from untrusted source. For example, SQLAlchemy happily allowed: `.order_by(text('id desc; DROP TABLE important_table;'))` to go into generated SQL, although some other error prevented this to be executed in my case. – Karol Zlot Sep 09 '21 at 10:15
3

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

YetAnotherDuck
  • 294
  • 4
  • 13
1

I have another example:

user = User.query.order_by(User.id.desc()).limit(10).all()

s.t

User is my class db.Modle

shreyasm-dev
  • 2,711
  • 5
  • 16
  • 34
Nima Fakoor
  • 11
  • 1