Mike, I'll try it and let you know the outcome (it may be a bit before I get to it, but I like your suggestions much better than having to use SQL views, which I am only doing due to a time crunch).
Much appreciated! On Wed, Mar 1, 2017 at 9:52 PM, mike bayer <mike...@zzzcomputing.com> wrote: > > > On 03/01/2017 10:22 PM, Greg Silverman wrote: > >> >> >> On Wed, Mar 1, 2017 at 8:53 PM, mike bayer <mike...@zzzcomputing.com >> <mailto:mike...@zzzcomputing.com>> wrote: >> >> >> >> On 03/01/2017 08:27 PM, GMS wrote: >> >> I have the following class models: >> >> >> | class DiagnosisDetail(Model): >> __tablename__ = 'vw_svc_diagnosis' >> diagnosis_id = Column(String(32), primary_key=True) >> first_name = Column(String(255)) >> last_name = Column(String(255)) >> mrn = Column(String(255)) >> dx_code = Column(String(255)) >> dx_id = Column(String(255), ForeignKey('dx_group.dx_id')) >> diagnosisgroup = relationship("DiagnosisGroup") >> dx_code_type = Column(String(255)) >> dx_name = Column(String(255)) >> >> __mapper_args__ = { >> "order_by":[mrn, dx_name] >> } >> >> class DiagnosisGroup(Model): >> __tablename__ = 'diagnosis_group' >> dx_id = Column(String(32), primary_key=True) >> mrn = Column(String(255)) >> dx_code = Column(String(255)) >> dx_code_type = Column(String(255)) >> dx_name = Column(String(255)) >> diagnosis_datetime = Column(DateTime) >> >> __mapper_args__ = { >> "order_by":[mrn, dx_name] >> }| >> >> >> >> where the underlying tables for DiagnosisGroup and >> DiagnosisDetail are >> SQL views. DiagnosisGroup is so that I can have a more succinct >> view of >> the data, since a patient can have the same diagnosis many >> times. I am >> wondering if there is a way to do this within the class structure >> instead of at the db server? >> >> >> do you mean, derive a DiagnosisGroup object from a DiagnosisDetail >> without running SQL? >> >> >> >> In as much as having the ORM do the work versus the backend, I guess. >> >> >> >> (or a list of them?) (the answer is..sure? just build Python code >> to generate objects from a list of DiagnosisDetail objects). >> >> >> >> >> Hmmm... but I don't get all the benefits of related data/data >> associations via key constraints that way with a non SQLA object. For >> example, I have a form that binds the Grouped records to their Detailed >> records in another form utilizing the one-to-many relationship between >> the two classes. >> > > > my example illustrates joining the two types of objects together in the > same way as a relationship-bound collection would. > > > >> >> >> >> I do not wish to do this through any ORM >> >> session queries, since these two classes have distinct use cases >> where >> they bind to wtform views. Thus, I would like to inherit the >> properties >> of these two classes from another distinct class. >> >> I have not been able to find anything like this, short >> of [create-column-properties-that-use-a-groupby][1] >> <http://stackoverflow.com/questions/25822393/how-can-i-creat >> e-column-properties-that-use-a-groupby/25879453 >> <http://stackoverflow.com/questions/25822393/how-can-i-creat >> e-column-properties-that-use-a-groupby/25879453>>, >> but this uses session queries to achieve the result. I would like >> to >> keep everything within the class itself through inheritance of the >> DiagnosisDetail class. >> >> >> You don't need a relational database to do grouping, if you have a >> list of data in memory it can be grouped using sets, or most >> succinctly Python's own groupby function: >> https://docs.python.org/2/library/itertools.html#itertools.groupby >> <https://docs.python.org/2/library/itertools.html#itertools.groupby> >> >> >> >> >> Indeed. I have used this for other things, but never thought of it for >> this case. >> >> >> >> >> >> >> >> >> >> Note: the primary key for DiagnosisGroup, is a concatenation of >> dx_code >> and another field patient_id. Groupings thus are unique. >> >> >> OK, so >> >> def keyfunc(detail): >> return (detail.dx_code, detail.patient_id) >> >> def get_diagnosis_groups(sorted_list_of_diagnosis_detail): >> >> for (dx_code, patient_id), details in >> itertools.groupby(sorted_list_of_diagnosisdetail, keyfunc): >> diagnosis_group = DiagnosisGroup( >> dx_code, patient_id >> ) >> diagnosis_group.details = details >> for detail in details: >> detail.group = diagnosis_group >> yield diagnosis_group >> >> >> >> >> Is there a way to use these as methods within a class model using the >> mapper, like in the stackoverflow link I gave? >> > > this functionality can be placed on a @property on your class, can be done > bidirectionally too. If you want a DiagnosisGroup to have a collection > of all the DiagnosisDetails on it you'd need to find a place to stash the > collection of all the DD objects you're dealing with in memory. > > > > >> Thanks for the out-of-the-box approach to thinking about this. >> >> Greg-- >> >> >> >> >> >> >> I do also need >> >> the FK relation back to the DiagnosisDetail class, which leads me >> to >> believe there should be three classes, where the two above classes >> inherit their properties from a parent class. >> >> >> >> >> >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, >> and >> Verifiable Example. See http://stackoverflow.com/help/mcve >> <http://stackoverflow.com/help/mcve> for a full >> description. >> --- >> You received this message because you are subscribed to the Google >> Groups "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from >> it, send >> an email to sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com> >> <mailto:sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com> >> <mailto:sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>>. >> Visit this group at https://groups.google.com/group/sqlalchemy >> <https://groups.google.com/group/sqlalchemy>. >> For more options, visit https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. >> >> >> -- >> SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve >> <http://stackoverflow.com/help/mcve> for a full description. >> --- You received this message because you are subscribed to a topic >> in the Google Groups "sqlalchemy" group. >> To unsubscribe from this topic, visit >> https://groups.google.com/d/topic/sqlalchemy/t124IuWhNI0/unsubscribe >> <https://groups.google.com/d/topic/sqlalchemy/t124IuWhNI0/unsubscribe >> >. >> To unsubscribe from this group and all its topics, send an email to >> sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>. >> Visit this group at https://groups.google.com/group/sqlalchemy >> <https://groups.google.com/group/sqlalchemy>. >> For more options, visit https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. >> >> >> >> >> -- >> Greg M. Silverman >> >> › flora-script <http://flora-script.grenzi.org/> ‹ >> * *› grenzi.org <http://grenzi.org/> › >> >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >> description. >> --- >> You received this message because you are subscribed to the Google >> Groups "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send >> an email to sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/to > pic/sqlalchemy/t124IuWhNI0/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- Greg M. Silverman › flora-script <http://flora-script.grenzi.org/> ‹ › grenzi.org › -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
