Thank a lot; that did it for me, seems I got very close with the explicit secondary join statement, but you finished it right off.
Just wanted to take this opportunity to say that I think the Python community is blessed with both your package, but potentially even more the effort you put in to make it work for (other) people. Keep up the good work, Thijs On Sun, 24 Oct 2010 12:16 -0400, "Michael Bayer" <[email protected]> wrote: > > On Oct 24, 2010, at 11:58 AM, [email protected] 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 [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
