Filter expression in an association proxy?

[Chuck Bearden]

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!
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)

[Michael Bayer]

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.

--
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.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Chuck Bearden]

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-conditions for an example of how this looks.

Perfect! Thank you for the pointer.