On Oct 24, 2010, at 11:58 AM, sqlalch...@lists.fastmail.net wrote: > I am trying to define relations between persons and teams, however there > are multiple possible relationships (player, reserve, coach, trainer). > My current set up defines both Person and Team using declarative base > classes. For completeness sake; the database is already defined by the > front end, thus SQLAlchemy is building on an existing database. In the > snippet below only useful columns are indicated.
Ultimately, you're probably going to want to use the association object pattern, since you probably will at some point need to access the "role" data itself, which is not available in results or for insert when you specify the table as "secondary". But your stated question is demonstrated by the following script, which uses the pattern illustrated at http://www.sqlalchemy.org/docs/orm/relationships.html#specifying-alternate-join-conditions-to-relationship : from sqlalchemy import * from sqlalchemy.orm import Session, relationship from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() team_person = Table('scheduler_team_person', Base.metadata, Column('team_id', Integer, ForeignKey('scheduler_team.id'), primary_key=True), Column('person_id', Integer, ForeignKey('scheduler_person.id'), primary_key=True), Column('role', String(length=1), primary_key=True), ) class Team(Base): __tablename__ = 'scheduler_team' id = Column(Integer, primary_key=True) name = Column(String(length=50)) class Person(Base): __tablename__ = 'scheduler_person' id = Column(Integer, primary_key=True) name = Column(String(length=50)) teams = relationship("Team", collection_class=set, secondary=team_person, secondaryjoin=and_( team_person.c.team_id==Team.id, team_person.c.role != 'T' ) ) engine = create_engine('sqlite://', echo=True) Base.metadata.create_all(engine) sess = Session(engine) t1, t2, t3, t4 = Team(), Team(), Team(), Team() p1, p2, p3, p4 = Person(), Person(), Person(), Person() sess.add_all([t1, t2, t3, t4, p1, p2, p3, p4]) sess.flush() sess.execute( team_person.insert(), [ {'team_id':t.id, 'person_id':p.id, 'role':role} for role in ['P', 'R', 'C'] for p in [p1, p2, p3, p4] for t in [t1, t2, t3] ] ) sess.execute( team_person.insert(), [ {'team_id':t4.id, 'person_id':p.id, 'role':'T'} for p in [p1, p2, p3, p4] ] ) for p in sess.query(Person): assert p.teams == set([t1, t2, t3]) > > class Team(Base): > __tablename__ = 'scheduler_team' > > id = Column(Integer, primary_key=True) > name = Column(String(length=50)) > > > class Person(Base): > __tablename__ = 'scheduler_person' > > id = Column(Integer, primary_key=True) > name = Column(String(length=50)) > > > team_person = Table('scheduler_team_person', Base.metadata, > Column('team_id', Integer, ForeignKey('scheduler_team.id')), > Column('person_id', Integer, ForeignKey('scheduler_person.id')), > Column('role', String(length=1)), > ) > > The association table contains an 'extra' field, role, in which the role > value is P, R, C, T (see roles indicated above). > > What I am currently trying to the is adding a relation(ship) to the > Person class, like this: > > teams = relationship( > "Team", > collection_class=set, > secondary=team_person, > ) > > However I would like to add another constraint; the relation above will > return all teams, no matter what the 'role' value is. I would like to > limit the role value (in my case to all but trainer), but have not been > successful to do so. Just to be clear; I would like the teams attribute > to contain all the team in which the person is either a player, reserve > or coach, but not trainer. I was hoping to alter the defined > relationship potentially using the explicit listing of primary and/or > second join, but have not been successful (the documentation also hints > to possible solutions using column_property). > > Would anyone be able to help out or hint me in the right direction? > > Thijs > > -- > 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.
