Joined table inheritance without a discriminator

[Ian]

All,

I have two applications: one uses Python with Sqlalchemy and the other
uses Java with Hibernate. There is a slight mis-match between the
joined table inheritance strategy: with Hibernate a discriminator is
not required.

The Sqlalchemy documentation says, in the Joined Table Inheritance
section (http://www.sqlalchemy.org/docs/05/mappers.html#joined-table-
inheritance):

"While there are some “tricks” to work around the requirement that
there be a discriminator column, they are more complicated to
configure when one wishes to load polymorphically."

What are these tricks and where are they documented. I'm not really
all that interested in polymorphic querying in the Python application.

Thanks,

Ian Johnson

[Michael Bayer]

Post by Ian
All,
I have two applications: one uses Python with Sqlalchemy and the other
uses Java with Hibernate. There is a slight mis-match between the
joined table inheritance strategy: with Hibernate a discriminator is
not required.
The Sqlalchemy documentation says, in the Joined Table Inheritance
section (http://www.sqlalchemy.org/docs/05/mappers.html#joined-table-
"While there are some “tricks” to work around the requirement that
there be a discriminator column, they are more complicated to
configure when one wishes to load polymorphically."
What are these tricks and where are they documented. I'm not really
all that interested in polymorphic querying in the Python application.

if you don't care about polymorphic querying, you should be able to leave
all the "polymorphic" options blank in your mapping. you would have to
take care to ensure your queries are only against specific subclasses, or
if against a superclass contains criterion that will limit the results to
only superclass instances.

the "tricks" at the moment are to provide a "view" that produces an
effective "polymorphic_on" column. It is necessary to create an OUTER
JOIN to all joined-subclass tables as well, which is how Hibernate's
version works, or alternatively to use a UNION that selects among joins.
We have a function that can generate the UNION version. I'm not sure
which is more efficient but they are both pretty awful.

i.e. in SQL, the OUTER JOIN version looks like:

SELECT base.*,
(CASE WHEN subtable1.id is not null THEN 'subtable1'
CASE WHEN subtable2.id is not null THEN 'subtable2'
CASE WHEN subtable3.id is not null THEN 'subtable3'
) AS polymorphic_type
subtable1.*,
subtable2.*,
subtable3.*
FROM base LEFT OUTER JOIN subtable1 ON base.id=subtable1.id
LEFT OUTER JOIN subtable2 ON base.id=subtable2.id
LEFT OUTER JOIN subtable3 ON base.id=subtable3.id

you'd create a selectable along the lines of :

join =
base.outerjoin(subtable1).outerjoin(subtable2).outerjoin(subtable3)
stmt = select([base, subtable1, subtable2, subtable3,
case([(subtable1.c.id!=None, "subtable1"), ...etc

for the UNION version the "polymorphic_union" function can generate the
right SQL expression here. you'd configure the base mapper the same way
the docs describe "polymorphic concrete loading", and your
polymprhic_union looks something like:

person_join = polymorphic_union(
{
'engineer':people.join(engineers),
'manager':people.join(managers),
'person':people.select(people.c.type=='person'),
}, None, 'pjoin')

Post by Ian
Thanks,
Ian Johnson
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group, send email to
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.