[Python] Order by association proxy: invalid sql

I want to attach a column of a related model to another model and use it like any other sqlalchemy column attribute. As mentioned in docs, association_proxy makes it easy to use.

from sqlalchemy.ext.associationproxy import association_proxy
from app import db


class User(db.Model):
id = db.Column(...)
name = db.Column(...)

class Book(db.Model):
author_id = db.Column(...)
author = db.relationship('User', foreign_keys=[author_id])
author_name = association_proxy('author', 'name')


But when I call Book.query.order_by(Book.author_name.asc()).all() I got this:

psycopg2.errors.SyntaxError: syntax error at or near "ASC"
LINE 4: ...user.id = book.author_id AND user.name ASC)


Sqlalchemy generates EXISTS statement:

...
EXISTS (SELECT 1
FROM user
WHERE user.id = book.author_id AND user.name ASC)


So how to use ordering properly with an associated attribute?

Continue reading...