On Oct 18, 2010, at 2:46 PM, Jasper K wrote:
> 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.
So "secondary" and "secondaryjoin" only apply to this exact pattern:
Parent -> association table -> Child
your example is doing this:
Parent -> association table -> Association Object
The correct mapping, using "secondary", would be:
mapper(Parent, parent_table, properties={
'nice_children': relation(Child, 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)
})
If you'd like to maintain that "nice_children" returns "Association" objects,
you'd have to get the link to "child" to be referenced inside the primaryjoin,
as the endpoint of the JOIN must be the target of the relation():
mapper(Parent, parent_table, properties={
'nice_children': relation(Association, lazy=True,
primaryjoin=and_(
parent_table.c.parent_id==association_table.c.parent_id,
association_table.c.child_id.in_(select([child_table.c.child_id]).where(child_table.c.status==NICE))
),
viewonly=True)
})
>
> 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.
>
--
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.
