This looks like an association proxy. Have you tried that?
http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html
--
Mike Conley
On Mon, Jun 1, 2009 at 11:46 PM, George Sakkis <george.sak...@gmail.com>wrote:
>
> I can't for the life of me figure out how to specify a relation
> spanning 3 tables. I think I've tried all combinations of
> primaryjoin, secondaryjoin, viewonly, foreign_keys, remote_dest and
> all that jazz, to no avail so far. If this is possible at all, there
> should be a sample usage at the docs or at least a recipe on the wiki
> since apparently it's not obvious at all. Below are some of my failed
> attempts; any help will be very much appreciated.
>
> George
>
> #====================================================
>
> from sqlalchemy.orm import relation, sessionmaker
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import create_engine, Column, Integer, String,
> ForeignKey, and_
>
> TEST = 5
>
> def main():
> c = Company()
> u = User()
> a = Address()
> c.employees.append(u)
> u.addresses.append(a)
> session = sessionmaker()()
> session.add(c)
> session.flush()
> print a.company
>
>
> Base = declarative_base(bind=create_engine('sqlite:///:memory:',
> echo=True))
>
> class Company(Base):
> __tablename__ = 'company'
> id = Column(Integer, primary_key=True)
> name = Column(String(50))
> employees = relation('User')
>
>
> class User(Base):
> __tablename__ = 'users'
> id = Column(Integer, primary_key=True)
> name = Column(String(50))
> company_id = Column(Integer, ForeignKey(Company.id))
> addresses = relation('Address')
>
>
>
> class Address(Base):
> __tablename__ = 'addresses'
> id = Column(Integer, primary_key=True)
> email = Column(String(50))
> user_id = Column(Integer, ForeignKey(User.id))
>
> # 1st attempt: just a primary join. Fails with
> # sqlalchemy.exc.ArgumentError: Could not locate any equated,
> locally
> # mapped column pairs for primaryjoin condition 'addresses.user_id
> = users.id
> # AND users.company_id = company.id' on relation Address.company.
> For more
> # relaxed rules on join conditions, the relation may be marked as
> viewonly=True.
> if TEST == 1:
> company = relation(Company,
> primaryjoin=and_(user_id==User.id,
>
> User.company_id==Company.id))
>
> # 2nd attempt: add viewonly=True. Fails with
> # sqlalchemy.exc.ArgumentError: Could not determine relation
> direction for
> # primaryjoin condition 'addresses.user_id = users.id AND
> users.company_id =
> # company.id', on relation Address.company. Specify the
> 'foreign_keys'
> # argument to indicate which columns on the relation are foreign.
> if TEST == 2:
> company = relation(Company,
> primaryjoin=and_(user_id==User.id,
>
> User.company_id==Company.id),
> viewonly=True)
>
> # 3rd attempt: add foreign_keys=[user_id]. Fails with
> #sqlalchemy.exc.ArgumentError: Remote column 'users.id' is not
> part of
> # mapping Mapper|Company|company. Specify remote_side argument to
> indicate
> # which column lazy join condition should bind.
> if TEST == 3:
> company = relation(Company,
> primaryjoin=and_(user_id==User.id,
>
> User.company_id==Company.id),
> viewonly=True,
> foreign_keys=[user_id])
>
> # 4th attempt: add remote_side=[Company.id]. Fails with
> # sqlalchemy.orm.exc.UnmappedColumnError: No column
> users.company_id is
> # configured on mapper Mapper|Address|addresses...
> if TEST == 4:
> company = relation(Company,
> primaryjoin=and_(user_id==User.id,
>
> User.company_id==Company.id),
> viewonly=True,
> foreign_keys=[user_id],
> remote_side=[Company.id])
>
> # 5th attempt: try secondary table/join. Fails with:
> # sqlalchemy.orm.exc.UnmappedColumnError: Column 'users.id' is not
> available,
> # due to conflicting property
> 'id':<sqlalchemy.orm.properties.ColumnProperty
> # object at 0x8f73bac>
> if TEST == 5:
> company = relation(Company, User.__table__,
> primaryjoin=user_id==User.id,
> secondaryjoin=User.company_id==Company.id)
>
> # give up :/
>
>
> if __name__ == '__main__':
> Base.metadata.create_all()
> main()
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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
-~----------~----~----~----~------~----~------~--~---
