有了这个,尝试考虑 SQLAlchemy 在尝试加载 User.dashboard 时应该发出什么。喜欢SELECT * FROM dashboard JOIN widget ... ORDER BY widget.sidebar
?或者SELECT * FROM dashboard ORDER BY (SELECT sidebar FROM widget...
?通过不同的表对结果进行排序对于工作来说太开放了relationship()
自己决定。可以做到这一点的方法是通过提供一个列表达式Dashboard
当 ORM 针对仪表板的表发出一个简单的 SELECT 时,以及当它在一个不那么简单的 SELECT 中引用它时,它可能会立即跨用户、仪表板表连接(例如,急切加载),可以提供此排序。
我们提供自定义 SQL 表达式,特别是那些涉及其他表的表达式,使用列属性() http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html?highlight=column_property#using-column-property,或者用延迟() http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html?highlight=column_property#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')
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
w1, w2 = Widget(name='w1', sidebar=1), Widget(name='w2', sidebar=2)
s.add_all([
User(login='u1', dashboard=[
Dashboard(
delta=1, widget=w1
),
Dashboard(
delta=2, widget=w2
)
]),
])
s.commit()
print s.query(User).first().dashboard
“.dashboard”负载发出的最终 SQL 是:
SELECT dashboard.user_id AS dashboard_user_id, dashboard.widget_id AS dashboard_widget_id, dashboard.delta AS dashboard_delta
FROM dashboard
WHERE ? = dashboard.user_id ORDER BY (SELECT widgets.sidebar
FROM widgets
WHERE widgets.id = dashboard.widget_id), dashboard.delta
请记住,MySQL 做了一个terrible像上面这样的子查询的工作优化。如果您在这里需要高性能,您可能会考虑将“sidebar”的值复制到“dashboard”中,尽管这会使一致性更难以维护。