Querying problem regarding joined table inheritance

[Matthias]

Hi,
i have a problem regarding inheritance. I use joined table
inheritance. Assume the classes parent, child1 and child2. The
parent_table has a field 'type'. I have to use
'session.query(child1).from_statement(deliver_me_parents_with
special_conditions).all()' - the result contains child1 and child2
objects. The documentation says sth. about bypassing all other stuff,
but my description was just a model for my problem, i can not do
anything like "type=child1" in the statment, cause there is a deeper
inheritance structure.

Summary: I have a result set of 'parents' but i want to get only the
child1 objects. The result of 'from_statement' contains the type-
column, so SQLAlchemy should be able to resolve the child1 objects?

Best regards,

Matthias.

[Michael Bayer]

it should. is child2 a subclass of child1 ?

[Matthias]

The answer to your question is sometimes yes and sometimes no.
I created a small example which illustrates my problems. Please keep
in mind that the 'from_statement' part is just a placeholder for
something which can not be replaced by anything.
If this can not be done the way i am trying at the moment, please give
me a hint for a solution where i do not have to find out the mapper/
class or table name.

I need something like
'session.query(AnyChildClass).from_statement(stmt).all()' and it
should deliver only objects of type 'AnyChildClass.'.

SQLAlchemy version: SQLAlchemy 0.4.8

My example for this topic:
-------------------------------------

# -*- coding: utf-8 -*-

"""
Small inheritance example for SQLAlchemy mailing list.
@see: http://groups.google.com/group/sqlalchemy/browse_thread/thread/1df94a3d59105688?hl=de
"""

from sqlalchemy import *
from sqlalchemy.orm import mapper, sessionmaker

# create engine, metadata and session
engine = create_engine('postgres://postgres@localhost/inheritance')
metadata = MetaData()
metadata.bind = engine
Session = sessionmaker(bind=engine)
session = Session()

# table definitions
parent_table = Table("parent", metadata,
Column("id", Integer, primary_key=True),
Column("type", String(30), nullable=False))

child1_table = Table("child1", metadata,
Column("id", Integer, ForeignKey("parent.id"), primary_key=True))

child2_table = Table("child2", metadata,
Column("id", Integer, ForeignKey("parent.id"), primary_key=True))

# class definitions
class Parent(object):
def __repr__(self):
return "%-10s, id: %s" %(self.__class__.__name__, self.id)

class Child1(Parent):
pass

class Child2(Parent):
pass

# mapper definitions
mapper(Parent, parent_table, polymorphic_on=parent_table.c.type, \
polymorphic_identity='parent')

mapper(Child1, child1_table, inherits=Parent, \
polymorphic_identity='child1')

mapper(Child2, child2_table, inherits=Parent, \
polymorphic_identity='child2')


# create db tables
metadata.create_all()

# create some objects and commit them to the db
p = Parent()
c1 = Child1()
c2 = Child2()

for obj in [p,c1,c2]:
session.save(obj)
session.flush()
session.commit()

# try to get only objects of Type 'Child1'
stmt = "SELECT * FROM parent"
result = session.query(Child1).from_statement(stmt).all()

# will print: (how can i solve this?)
# Parent , id: 1
# Child1 , id: 2
# Child2 , id: 3
for obj in result:
print obj

[Michael Bayer]

seems like "SELECT * from parent WHERE type='child1'" should do it
there. If you were to say sess.query(Child1).all() it would do a
JOIN to limit the results, if you want to go that route you'd say
"SELECT * from parent JOIN child1 ON parent.id=child1.id".

So to answer your question, with the configuration given, there's no
way for the polymorphic load to return only Child1 objects if the
corresponding SQL is returning other kinds of rows. As an
alternative you can create a non-primary mapper that is only against
"parent join child1" (i.e. mapper(Child1, parent.join(child1),
non_primary=True) ) and query using that mapper - but that will still
interpret rows which correspond to Parent or Child2 objects as Child1
objects, which is "incorrect" by most standards. If OTOH you really
want just the rows that have "child1" as a type, and for some reason
you can't establish that criteria in the SQL, then you'd need to
filter the results after the fact.

though I can't imagine a scenario where an existing SQL statement is
present that cannot have a simple "WHERE type='child1'" added to it.

[Matthias]

At the moment i use a workaround which does exactly that what you
mentioned. It is a 'wanted class' query string discriminator.
With the example this would work properly. The only thing i do not
know is how to get the 'polymorphic_identity' which is defined in the
mapper. How can i retrieve this String when only the class is given?
Sth. like getMapper().getPolymorphicIdentity(), i just do not know how
to get it? Of course you could create your own conventions and use
alway sth. like 'classname.lowercase.lower()'.

But there is still my bigger problem, of course we have to modify the
scenario:

class Parent(object):
class Child(Parent):
class SubChild1(Child):
class SubChild2(Child):

Now i want to get all Child objects, this means all Child, Subchild1
and Subchild2 objects.

result = session.query(Child).from_statement(stmt).all()

The parent class of Subchild1 and Subchild2 - Child does not know
anything about any subclasses.
So if i only have the Child class i can not imagine a smart way to
solve this, because in plain SQL i need all possible type values.
The nice thing about SQLAlchemy was the automatic inheritance
resolver.

Would be very nice if you could teach me a smart way to achieve this.

Thanks,

Matthias.

> > @see:http://groups.google.com/group/sqlalchemy/browse_thread/thread/1df94a...

[Michael Bayer]

On Oct 27, 2008, at 4:57 PM, Matthias wrote:

>
> At the moment i use a workaround which does exactly that what you
> mentioned. It is a 'wanted class' query string discriminator.
> With the example this would work properly. The only thing i do not
> know is how to get the 'polymorphic_identity' which is defined in the
> mapper. How can i retrieve this String when only the class is given?

you could say "class_mapper(MyClass).polymorphic_identity".

>
> But there is still my bigger problem, of course we have to modify the
> scenario:
>
> class Parent(object):
> class Child(Parent):
> class SubChild1(Child):
> class SubChild2(Child):
>
> Now i want to get all Child objects, this means all Child, Subchild1
> and Subchild2 objects.
>
> result = session.query(Child).from_statement(stmt).all()
>
> The parent class of Subchild1 and Subchild2 - Child does not know
> anything about any subclasses.
>
> So if i only have the Child class i can not imagine a smart way to
> solve this, because in plain SQL i need all possible type values.
> The nice thing about SQLAlchemy was the automatic inheritance
> resolver.

the Query() object, if you were not using from_statement(), would
generate SQL that joins from the parent to child table and returns all
rows. This returns for you all Child, SubChild1, and SubChild2
objects. parent rows which are not child rows wont be returned since
they do not join to child. The remaining columns from subchild1 and
subchild2 are loaded in separate SELECT statements.

If OTOH you were using single table inheritance, the Query would in
fact produce a clause like parent.type.in_('child', 'subchild1',
'subchild2').

But for joined table inheritance, the join you're looking for here is:

select * from parent JOIN child on parent.id=child.id

that way you also don't need to know the discriminator.

[Matthias]

Ok, the real world structure was a little bit more comnplicated, but
it worked out this way.
There is only one problem left, regarding the the name of the FK
column which is responsible for resolving the inheritance.
For constructing my join condition, i need to know the name of that
column. Sometimes it is id, sometimes it is for example foo_id.

I searched with

print dir(class_mapper(MyClass).mapped_table)
print dir(class_mapper(MyClass).local_table)

and so on...
I did not find a way to resolve the name of the column which is
responsible for inheritance.

[Michael Bayer]

On Oct 28, 2008, at 12:28 AM, Matthias wrote:

>
> Ok, the real world structure was a little bit more comnplicated, but
> it worked out this way.
> There is only one problem left, regarding the the name of the FK
> column which is responsible for resolving the inheritance.
> For constructing my join condition, i need to know the name of that
> column. Sometimes it is id, sometimes it is for example foo_id.
>
> I searched with
>
> print dir(class_mapper(MyClass).mapped_table)
> print dir(class_mapper(MyClass).local_table)
>
> and so on...
> I did not find a way to resolve the name of the column which is
> responsible for inheritance.

you can get the full join condition (the onclause) with
class_mapper(MyClass).inherit_condition. You can also just join the
tables in question together using table1.join(table2) and if the
foreign key relation between them is unambiguous, the onclause is
generated automatically (which is the same way inherit_condition is
determined).