I worked on this some more and found that the queries are correct as 
they are.  The problem is in the mapping.  A Chemical Engineer is an 
Engineer, who is an Employee.  So the employees selectable should return 
all employees and the engineer selectable should return all engineers, 
not just unspecific engineers.

For the purpose of discussion and example, assume all engineers must be 
specified.  They may be chemical or mechanical engineers.  I think the 
polymorphic union should look like this:

p_union = polymorphic_union(
     'ac': accountants,
     'me': mechanical_engineers,
     'ce': chemical_engineers,

Notice that I didn't include the employee table nor the engineer 
selectable.  That's because I don't want instances of the Employee class 
or the Engineer class.  They would cause redundant data since a Chemical 
Engineer is an Engineer is an Employee, etc.

That leads to the part I'm stuck on; mapper inheritance.  When finished, 
session.query(Employee).select() should list all employee as instances 
of their specific classes and session.query(Engineer).select() should 
list all engineers ...  So how do I set up the mappers to accomplish 
this?  The polymorphic_on/polymorphic_identity method seems to only 
accommodate 1 level of inheritance.  The Engineer mapper will inherit 
from the Employee mapper, but will have no polymorphic_identity.

Summary of design:

* Single employees table with ac_info, ce_info, and me_info columns.
* Selectables for each employee type that looks like this:
     engineers = select([employees, ],
         and_(employees.c.engineer_info != None,
     chemical_engineers = select([employees,
         and_(employees.c.engineer_info != None,
         employees.c.ce_info != None)).alias('chemical_engineers')
* Polymorphic Union that only includes "types" for instances we want
   (No Employee or Engineer) types.

So how could mappers be set up to accommodate these criteria?


Michael Bayer wrote:
> if you change your "echo" to 'debug', or just select straight from your
> p_union selectable, youll see these rows:
> (5, u'cengineer1', u'cengineer1', u'cengineer1', None,
> u'chemical_engineer')
> (6, u'cengineer2', u'cengineer2', u'cengineer2', None,
> u'chemical_engineer')
> (1, u'manager1', None, None, u'manager1', u'manager')
> (2, u'manager2', None, None, u'manager2', u'manager')
> (3, u'engineer1', u'engineer1', None, None, u'engineer')
> (4, u'engineer2', u'engineer2', None, None, u'engineer')
> (5, u'cengineer1', u'cengineer1', u'cengineer1', None, u'engineer')
> (6, u'cengineer2', u'cengineer2', u'cengineer2', None, u'engineer')
> Where you can see that the chemical engineers are coming out twice with
> inconsistent types.  the query has to be tuned to be more specific:
> managers = select([employees, column("'manager'").label('type')],
> employees.c.manager_data !=
>                                None).alias('managers')
> engineers = select([employees, column("'engineer'").label('type')],
> and_(employees.c.engineer_info !=
>                                 None,
> employees.c.cheme_info==None)).alias('engineers')
> chemical_engineers = select([employees,
> column("'chemical_engineer'").label('type')],
>                              and_(employees.c.engineer_info != None,
>                                   employees.c.cheme_info !=
> None)).alias(
>                                   'chemical_engineers')
> p_union = polymorphic_union(
>     {
>     'engineer': engineers,
>     'manager': managers,
>     'chemical_engineer': chemical_engineers
>     },
>     None, 
> )

 You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 

Reply via email to