On Oct 18, 4:06 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> 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)
> })
>
Yes, I would like to maintain the Association Object pattern since
there is other data that is stored in the association table.
This second approach works well, thanks for your help.
>
>
> > 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
> > athttp://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.
