On Jun 2, 7:00 am, Mike Conley <mconl...@gmail.com> wrote:
> This looks like an association proxy. Have you tried that?
>
> http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html
I don't see how this is related. From a quick read, it seems that's a
simplified way to handle many-to-many relations by hiding the
association object and it requires a regular relation to be defined
anyway. In my example there are two independent one-to-many
relationships and the intermediate table (User) is not just an
association object, it's a standalone object.
George
> 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
-~----------~----~----~----~------~----~------~--~---
