I don't know if this is a bug or I don't understand eager loading
When I try to eager load a relation containing an order_by clause, the
generated SQL fails. It seems that the generated SQL does not account for
the fact that the table is internally aliased by SQLAlchemy in the query.
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.5-py2.5.egg\sqlalchemy\engine\base.py",
line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) no such column:
bullets.position u'SELECT slides
.id AS slides_id, slides.name AS slides_name, bullets_1.id AS bullets_1_id,
bullets_1.slide_id AS bu
llets_1_slide_id, bullets_1.position AS bullets_1_position, bullets_1.text
AS bullets_1_text \nFROM
slides LEFT OUTER JOIN bullets AS bullets_1 ON slides.id =
bullets_1.slide_id ORDER BY bullets.posit
ion' []
I am using SQLAlchemy 0.5.5 with a SQLite database
Here is my sample, based on the Slides and Bullets example from the
orderlinglist documentation:
class Slide(Base):
__tablename__ = 'slides'
id = Column(Integer, primary_key=True)
name = Column(String)
bullets = relation('Bullet', lazy=False, cascade='all,delete-orphan',
collection_class=ordering_list('position', count_from=1),
order_by=['bullets.position'])
class Bullet(Base):
__tablename__ = 'bullets'
id = Column(Integer, primary_key=True)
slide_id = Column(Integer, ForeignKey('slides.id'))
position = Column(Integer)
text = Column(String)
print '# create a Slide with 3 bullets'
s=Slide(name='Slide 1')
for n in range (1,3):
b=Bullet(text='bullet %s'%n)
s.bullets.append(b)
session.add(s)
# add another to make sure we don't get cross joins
session.add(Slide(name='Slide 2'))
session.commit()
# print the slides
query = session.query(Slide) # <-- generates bad SQL
for s in query.all():
print s.name
for b in s.bullets:
print ' ',b.position,b.text
It seems the error is avoided if I remove lazy=False from the relation and
use contains_eager() on queries.
query =
session.query(Slide).outerjoin(Slide.bullets).order_by(Slide.name,Bullet.position)
query = query.options(contains_eager(Slide.bullets))
The problem with this is that I lost much of the relational simplicity. I
had to add join and ordering
clauses to my query that were already specified in the relation definition.
Am I missing something? Maybe another way of specifying order_by in the
relation?
--
Mike Conley
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
