CODEWITHSUNDEEP
pythonmysqlsqlalchemysql-order-byflask-sqlalchemy
Sasha
Sasha

Reputation: 367

SQLAlchemy MySQL complex ORDER BY

I have 2 models:

class Actor(db.Model):
    ...
    edited_at = db.Column(db.DateTime)

class Publication(db.Model):
    ...
    published_at = db.Column(db.DateTime, default=datetime.now)
    actor_id = db.Column(db.Integer, db.ForeignKey('actor.id'))

And I need to order actors by either actor.edited_at or publication.published_at field (latest date has priority). Not sure if I can accomplish that in SQLAlchemy or I need to use raw SQL (raw SQL query would be a solution too, btw).

What I have now:

(db.session.query(models.Actor, func.max(models.Publication.published_at).label('latest')).join(models.Publication).group_by(models.Actor).order_by('latest desc', Actor.edited_at.desc())).all()

Upvotes: 0

Views: 430

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

You could use a CASE expression to choose what to order by, though that's not particularly index friendly:

from sqlalchemy import case

latest = func.max(models.Publication.published_at)

db.session.query(
        models.Actor,
        latest.label('latest')).\
    join(models.Publication).\
    group_by(models.Actor).\
    order_by(case(
        [(latest > models.Actor.edited_at, latest)],
        else_=models.Actor.edited_at).desc()).\
    all()

Alternatively you could use GREATEST():

order_by(func.greatest(latest, models.Actor.edited_at).desc())

Upvotes: 2

Related Questions