SQLAlchemy: 关系中的关系字段排序

在一个 Pyramid 应用程序中，遇到以下场景：

class Widget(Base):
    __tablename__ = 'widgets'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    sidebar = Column(mysql.TINYINT(2))

    def __init__(self, name, sidebar):
        self.name = name
        self.sidebar = sidebar

class Dashboard(Base):
    __tablename__ = 'dashboard'
    user_id = Column(Integer, ForeignKey('users.id'), primary_key=True)
    widget_id = Column(Integer, ForeignKey('widgets.id'), primary_key=True)
    delta = Column(mysql.TINYINT)

    widget = relationship('Widget')

    def __init__(self, user_id, widget_id, delta):
        self.user_id = user_id
        self.widget_id = widget_id
        self.delta = delta

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    login = Column(Unicode(255), unique=True)
    password = Column(Unicode(60))
    fullname = Column(Unicode(100))

    dashboard = relationship('Dashboard', order_by='Dashboard.widget.sidebar, Dashboard.delta')

    def __init__(self, login, password, fullname):
        self.login = login
        self.password = crypt.encode(password)
        self.fullname = fullname

希望 User 的 dashboard 关系具有按 sidebar（这是 Dashboard 的关系属性）排序的仪表板记录。但是，在使用这种方式排序时，会收到以下错误：

sqlalchemy.exc.InvalidRequestError: Property 'widget' is not an instance of ColumnProperty (i.e. does not correspond directly to a Column).

2、解决方案
为了解决这个问题，可以使用 column_property() 或 deferred() 来为 User.dashboard 定义一个自定义排序属性。

使用 column_property()：

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Widget(Base):
    __tablename__ = 'widgets'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    sidebar = Column(Integer)

class Dashboard(Base):
    __tablename__ = 'dashboard'
    user_id = Column(Integer, ForeignKey('users.id'), primary_key=True)
    widget_id = Column(Integer, ForeignKey('widgets.id'), primary_key=True)
    delta = Column(Integer)

    widget = relationship('Widget')

    widget_sidebar = column_property(
        select([Widget.sidebar]).where(Widget.id == widget_id)
    )

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    login = Column(Unicode(255), unique=True)

    dashboard = relationship('Dashboard', order_by='Dashboard.widget_sidebar, Dashboard.delta')

使用 deferred()：

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Widget(Base):
    __tablename__ = 'widgets'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    sidebar = Column(Integer)

class Dashboard(Base):
    __tablename__ = 'dashboard'
    user_id = Column(Integer, ForeignKey('users.id'), primary_key=True)
    widget_id = Column(Integer, ForeignKey('widgets.id'), primary_key=True)
    delta = Column(Integer)

    widget = relationship('Widget')

    widget_sidebar = deferred(
        select([Widget.sidebar]).where(Widget.id == widget_id)
    )

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    login = Column(Unicode(255), unique=True)

    dashboard = relationship('Dashboard', order_by='Dashboard.widget_sidebar, Dashboard.delta')

使用这些代码，可以按 sidebar 对 User.dashboard 中的仪表板记录进行排序。