On Thursday, January 3, 2013 3:13:58 PM UTC-6, Michael Bayer wrote: > > > 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-conditionsfor > an example of how this looks. >
Perfect! Thank you for the pointer. 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 sqlal...@googlegroups.com <javascript:> . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com <javascript:>. 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 view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/aBICNiOX-yYJ. 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.