CODEWITHSUNDEEP
pythonsqlormsqlalchemyflask-sqlalchemy
Andrii Matiiash
Andrii Matiiash

Reputation: 568

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?

Upvotes: 1

Views: 498

Answers (1)

codeape
codeape

Reputation: 100866

In this case it is probably best to use a column_property:

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'

    id = Column(Integer(), primary_key=True)    
    name = Column(Unicode())


class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer(), primary_key=True)
    author_id = Column(Integer(), ForeignKey('user.id'), nullable=False)
    author = relationship('User')


Book.author_name = column_property(select([User.name]).where(User.id == Book.author_id))


# sample ORM query
books = session.query(Book).order_by(Book.author_name)

Upvotes: 1

Related Questions