Hi Group, I am using SqlAlchemy 0.5.8
I get an "(OperationalError) no such column: child.child_id" error when trying to eagerload a viewonly many-to-many relationship that uses the Association Object pattern with a custom secondaryjoin condition (http://www.sqlalchemy.org/docs/05/mappers.html#association- object). The following example demonstrates the issue. It consists of three tables: Parent, Child, Association. The goal is to have a mapped property on the Parent table that filters out certain associations based on properties stored in the Child table (the Parent is trying to eagerload all it's "nice children"). It seems the SQL that gets generated during the eagerload fails to include the Child table. However when used without the eagerload everything works as expected. I have not tried this with the newer SqlAlchemy but I would like to get it working on 0.5.8 if possible. Any help is much appreciated, Thanks from sqlalchemy import Table, Column, String, Integer, MetaData, ForeignKey, create_engine, and_ from sqlalchemy.orm import mapper, relation, sessionmaker, eagerload engine = create_engine('sqlite:///:memory:', echo=True) metadata = MetaData() Session = sessionmaker(bind=engine) session = Session() NICE = 'NICE' NAUGHTY = 'NAUGHTY' parent_table = Table('parent', metadata, Column('parent_id', Integer, primary_key=True), Column('name', String(32))) child_table = Table('child', metadata, Column('child_id', Integer, primary_key=True), Column('name', String(32)), Column('status', String(32))) association_table = Table('association', metadata, Column('id', Integer, primary_key=True), Column('parent_id', ForeignKey('parent.parent_id')), Column('child_id', ForeignKey('child.child_id'))) class Parent(object): def __init__(self, name): self.name = name class Child(object): def __init__(self, name, status): self.name = name self.status = status class Association(object): def __init__(self, parent, child): self.parent = parent self.child = child mapper(Parent, parent_table, properties={ 'nice_children': relation(Association, lazy=True, secondary=association_table, primaryjoin=parent_table.c.parent_id==association_table.c.parent_id, secondaryjoin=and_(association_table.c.child_id==child_table.c.child_id, child_table.c.status==NICE), viewonly=True) }) mapper(Association, association_table, properties={ 'child': relation(Child), 'parent': relation(Parent) }) mapper(Child, child_table) metadata.create_all(engine) # CREATE ALL THE OBJECTS jack = Parent('Jack') kid1 = Child('Kid1', NICE) kid2 = Child('Kid2', NAUGHTY) session.add_all([jack, kid1, kid2]) session.flush() assoc1 = Association(jack, kid1) assoc2 = Association(jack, kid2) session.add_all([assoc1, assoc2]) session.flush() assert jack.nice_children session.expunge_all() # TEST THE EAGERLOADING parents = session.query(Parent).all() assert parents assert parents[0].nice_children[0].child.name == 'Kid1' # fails parents = session.query(Parent).options(eagerload('nice_children')).all() assert parents assert parents[0].nice_children[0].child.name == 'Kid1' -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.
