In a Pyramid application I'm working on, I have the following scenario:
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
So, I want the User 'dashboard' relationship to have the dashboard records for the user but ordered by 'sidebar' (which is a relationship property of Dashboard). Currently I am getting this error:
sqlalchemy.exc.InvalidRequestError: Property 'widget' is not an instance of ColumnProperty (i.e. does not correspond directly to a Column).
Is this ordering possible in a relationship declaration?
Thanks!
With this, try to think what SQL SQLAlchemy should emit when it tries to load User.dashboard. Like SELECT * FROM dashboard JOIN widget ... ORDER BY widget.sidebar
? Or SELECT * FROM dashboard ORDER BY (SELECT sidebar FROM widget...
? ordering the results by a different table is too open-ended of a job for relationship()
to decide on it's own. The way this can be done is by providing a column expression in terms of Dashboard
that can provide this ordering, when the ORM emits a simple SELECT against dashboard's table, as well as when it refers to it in a not-so-simple SELECT where it might be joining across User, Dashboard tables at once (e.g. eager loading).
We provide custom SQL expressions, particularly those that involve other tables, using column_property(), or alternatively with deferred() when we don't want that expression to be loaded by default (as is likely the case here). Example:
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
the final SQL emitted by the load of ".dashboard" is:
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
Keep in mind that MySQL does a terrible job optimizing for subqueries like the one above. If you need high performance here, you might consider copying the value of "sidebar" into "dashboard", even though that makes consistency more difficult to maintain.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With