Problems eagerloading a set of object using SQLAlchemy

Question

I'm using Turbogears2 and SQLAlchemy to develop a webapp. I have two mapped tables O1 and O2. O2 has a sorted list of O1s in 'ones'. At some point I want to query all O2's and the referenced O1's.

Unfortunately the query below fails because table O2 is aliased in the query and the column referenced by the order_by phrase is no longer known.

I'd like to know how I can fix this problem while, if possible, staying in the declarative syntax.

base = declarative_base()

class O1(base):
    __tablename__ = 'O1'
    value = Column(Integer)
    o2_id = Column(Integer, ForeignKey('O1.id')) # The culprit

class O2(base):
    __tablename__ = 'O2'
    id = Column(Integer, primary_key=True)
    ones = relation('O1', order_by = ['O1.value'])


Session.query(O2).options(eagerload('ones')).all() # Throws an error

Ants Aasma · Accepted Answer · 2009-05-19 22:08:02Z

3

Use a lambda of clause elements to achieve late binding of the order by, like this:

ones = relation('O1', order_by=lambda:[O1.value])

Or as an another option, make the whole order_by a string, like this:

ones = relation('O1', order_by='O1.value, O1.something_else')

answered May 19, 2009 at 22:08

Ants Aasma

55.3k16 gold badges98 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ebo Over a year ago

Thanks. Both solutions work. I'll use the second one. One addition because it is ambiguous: When using strings you have to use the mapper names. The 'O1' in the second example actually refers to the class, not the tablename.

Collectives™ on Stack Overflow

Problems eagerloading a set of object using SQLAlchemy

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related