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 -~----------~----~----~----~------~----~------~--~---