On Jan 3, 2013, at 4:01 PM, Chuck Bearden wrote: > Python 2.7.2 > SQLAlchemy 0.8.0b2 > Ubuntu 11.10 > > Consider the script below. The idea is that I have a medical record, and I > want to store diagnoses of two kinds: referring diagnoses and post-evaluation > diagnoses. I want to store them in a single table (this constraint is given > by the project I inherited). By means of the relationships & association > proxies defined below, I can correctly store diagnoses of both sorts. > However, I am unable to retrieve only the diagnoses of one sort or the other > by means of the proxy. Is there a way to incorporate a filter expression in a > relationship/association_proxy to enable retrieval of only certain values > from the proxied table? Or are there other techniques (short of creating > separate tables for referring & post-evaluation diagnoses)? > > Thanks for any suggestions!
the ultimate relationship() that's being proxied needs to filter on the "dxtype" attribute using a custom primaryjoin condition. See http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#specifying-alternate-join-conditions for an example of how this looks. > Chuck > > #----- script ------# > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy import create_engine > from sqlalchemy.orm import sessionmaker > from sqlalchemy import ( > Column, > Integer, > ForeignKey, > String, > ) > from sqlalchemy.ext.associationproxy import association_proxy > > from sqlalchemy.orm import relationship > Base = declarative_base() > > class MedicalRecord(Base): > __tablename__ = 'medicalrecord' > id = Column(Integer, primary_key=True) > patientname = Column(String(80)) > # Use the Diagnoses table with dxtype='referring' > rel_referring_diagnoses = relationship( > "Diagnoses", > cascade="all,delete-orphan" > ) > referring_diagnoses = association_proxy( > 'rel_referring_diagnoses', > 'diagnosis', > creator=lambda dx: Diagnoses(diagnosis=dx, dxtype='referring') > ) > # Use the Diagnoses table with dxtype='posteval' > rel_posteval_diagnoses = relationship( > "Diagnoses", > cascade="all,delete-orphan" > ) > posteval_diagnoses = association_proxy( > 'rel_posteval_diagnoses', > 'diagnosis', > creator=lambda dx: Diagnoses(diagnosis=dx, dxtype='posteval') > ) > > > class Diagnoses(Base): > __tablename__ = 'diagnoses' > id = Column(Integer, primary_key=True) > patient_id = Column(Integer, ForeignKey('medicalrecord.id', \ > ondelete='cascade')) > diagnosis = Column(String(80)) > dxtype = Column(String(40)) > > if __name__ == '__main__': > engine = create_engine('postgresql://foo:bar@localhost/baz') > Session = sessionmaker(bind=engine) > session = Session() > Base.metadata.create_all(engine) > > new_record = MedicalRecord() > new_record.patientname = 'Fred' > session.add(new_record) > new_record.referring_diagnoses.append('runny nose') > session.commit() > # This will print 'runny nose'; I want it to print nothing > print new_record.posteval_diagnoses > # Database has: > # baz=> select * from diagnoses; > # id | patient_id | diagnosis | dxtype > # ----+------------+------------+----------- > # 1 | 1 | runny nose | referring > # (1 row) > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/WEfhR1WcGosJ. > 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.
