The issue with using the foreign_key integer value as the discriminator is that you won't know what that is at class declaration time. The type_name, however, you can declare as a part of the class as you would with a normal string discriminator. I'm not sure how you would do a correlated subquery for the polymorphic_on attribute. I modified the query object so that I could filter results for the derived classes. It doesn't seem like that happens automatically. Maybe I'm just not doing it right.
On Tue, Nov 29, 2016 at 7:16 AM, mike bayer <mike...@zzzcomputing.com> wrote: > > > On 11/28/2016 05:58 PM, Tucker Beck wrote: > >> Hello, I'm writing today about an interesting problem we ran into with >> our sqlalchemy based data store api. >> >> Our schema is based on the star-schema idea where we have a large 'fact >> table' with lots of rows. Within that table, each row has a foreign key >> to a small 'dimension table' in which each row has a unique name. Thus, >> the type of each row can be defined by the relationship between the fact >> row and the dimension row. >> >> We wanted to be able to use SQLAlchemy to add some custom functionality >> for the different types of rows we have in our 'fact table'. After >> learning about SQLAlchemy's inheritance models, I decided to see if we >> could support the schema that we had already devised for our project. >> The single-inheritance pattern seemed to fit the best, but I couldn't >> find a single case where someone was using a star-schema and needed the >> type discriminator to be derived from the foreign key to the dimension >> table. >> >> Further, I found as I was digging into the mechanics of the thing that >> you could not create a row in the fact table that was typed by the >> derived class at creation time. And, you cannot limit queries from the >> fact table by creating the queries against the derived classes. Suppose >> that (using declarative base) I have the fact table represented by a >> model called HybridModel. This model has two derived classes HybridAlpha >> and HybridBeta. I would like to be able to create a new row in the table >> wrapped by HybridModel by calling something like HybridAlpha(**kwargs) >> and have the type of the new row reflect the inheritance model I've >> described above. Next I wanted to be able to formulate a query against >> one of the derived models and have it limited by the type associated >> with the derived class. So, calling something like >> `session.query(HybridAlpha).all()` would only return rows with a type >> associated with the HybridAlpha model. >> >> After a lot of tinkering and experimentation, I've come up with the >> following solution: >> >> https://gist.github.com/dusktreader/2658dedb8ca8e2fd941f8075c75b3c89 >> >> I would appreciate any thoughts and feedback on the matter. I'm not sure >> that my approach to this solution has been sound, and I would appreciate >> feedback. >> > > I'd try to do things much more simply than this. Most simply, just > assign polymorphic_identity to be the integer foreign key value. Otherwise, > you should be able to do polymorphic_on on a correlated subquery, which > itself you set up as a column_property(). There should be no need to > modify Query or anything like that. > > > I'm out of time today but if you need more help I can try to work up an > example later on. > > > > > > >> Thanks, and keep up the great work! SQLAlchemy is just magic! >> >> -- >> 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/KJXSHwbhbLA/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. > -- -=Tucker A. Beck=- Illustrious Writer Devious Coder Last Hope for the Free World Also, Modest -- 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.
