1

Answer no longer needed as I changed focus in code. (see my comment in answer) Post answers for future reference...

How do I retrieve results from a one to many backref ordered by a field in the child? I need all somethings for the gid ordered by index. But at this time they are retrieved randomly even though they are ordered in the ms sql server.

I have in TurboGears 2 datamodels.py:

`class Parcel(DeclarativeBase):
    __tablename__ = 'GENERAL'
    __table_args__ =  ({'autoload': True})   

    gid = Column(Integer, primary_key=True)`
    somethings = relationship('Something', backref='Parcel')

 'class Something(DeclarativeBase):
    __tablename__ = 'SKETCH'
    __table_args__ =  ({'autoload': True})

    gid = Column(Integer, ForeignKey('GENERAL.gid'), primary_key=True)
    index = Column(Integer, primary_key=True)

In Turbogears root.py:

query = DBSession.query(Parcel)
query = query.options(joinedload('somethings')
query=session.filter(Parcel.gid==gid)

Returns all somethings for gid unordered.

stuartz
  • 121
  • 4
  • 9

1 Answers1

1

DBSession.query(Something).filter_by(gid=gid).order_by(Something.index).all()

edit: relationship() accepts a keyword argument order_by to order instances when you use the relationship. If you want to specify the ordering for the reverse direction, you can use the backref() function instead of the backref keyword and use the same order_by keyword argument as with relationship().

Simon
  • 12,018
  • 4
  • 34
  • 39
  • but then it is no longer used as a backref, but becomes and individual query and needs to be added to the context dict or Parcel query. I am looking for a way to order the backref which is already added to the Parcel query. There may not be an easy way to do it other than its own query. – stuartz Oct 19 '12 at 13:02
  • I was never able to order by a field on the child. The backref ordered the child by a field from the parent. And I could not get backref=backref() to work correctly either. I ended up doing separate queries...which I also did on other relations that were working correctly except when too much information caused turbogears to crash. The individual queries seemed to run quicker and prevented crashing. Thanks for responding. – stuartz Oct 23 '12 at 13:40