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 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.
