Mike Conley wrote:
> I don't know if this is a bug or I don't understand eager loading
eager loading doesn't understand your string "bullets.position" - it
renders it directly as you've specified and can't do anything intelligent
with it such as alias it. Specify it as bullets.c.position, or to have
declarative evaluate it upon mapper compilation,
order_by="Bullet.position".
>
> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
