On 10/5/15 2:51 PM, Will Angenent wrote:
I have run into an interesting condition when using joined table
inheritance. For example we have the typical employee, manager and
engineer tables with the following relationships:
- an engineer is an employee
- a manager is an employee
- an employee has a manager
We find some interesting results when using this query:
session.query(Employee.id, Engineer.id).join(Engineer.manager)
The resulting SQL is:
SELECT employee.id AS employee_id, engineer.id AS engineer_id
FROM engineer, employee JOIN (employee AS employee_1 JOIN manager AS
manager_1 ON manager_1.id = employee_1.id) ON manager_1.id =
employee.manager_id
The system here is going to see that you'd like to JOIN from Employee.id
to Engineer.manager, which is really Employee.manager, so you get
"employee JOIN (employee JOIN manager)", then the presence of
Engineer.id is not at all recognized as part of the same thing so it
isn't made to be part of the join.
that is, query.join() always looks at the first thing in the columns
list to join *from*, unless you use select_from().
So fix is:
1. session.query(Employee.id,
Engineer.id).select_from(Engineer).join(Engineer.manager)
2. ent = with_polymorphic(Employee, [Engineer], innerjoin=True)
session.query(ent.id, ent.Engineer.id).join(ent.manager)
e.g.:
test(5, session.query(Engineer.id,
Employee.id).select_from(Engineer).outerjoin(Engineer.manager))
test(6, session.query(Employee.id,
Engineer.id).select_from(Engineer).outerjoin(Engineer.manager))
from sqlalchemy.orm import with_polymorphic
ent = with_polymorphic(Employee, [Engineer], innerjoin=True)
test(7, session.query(ent.Engineer.id, ent.id).outerjoin(ent.manager))
test(8, session.query(ent.id, ent.Engineer.id).outerjoin(ent.manager))
output:
Test 5
SELECT engineer.id AS engineer_id, employee.id AS employee_id
FROM employee JOIN engineer ON engineer.id = employee.id LEFT OUTER JOIN
(employee AS employee_1 JOIN manager AS manager_1 ON manager_1.id =
employee_1.id) ON manager_1.id = employee.manager_id
2
Test 6
SELECT employee.id AS employee_id, engineer.id AS engineer_id
FROM employee JOIN engineer ON engineer.id = employee.id LEFT OUTER JOIN
(employee AS employee_1 JOIN manager AS manager_1 ON manager_1.id =
employee_1.id) ON manager_1.id = employee.manager_id
2
Test 5
SELECT engineer.id AS engineer_id, employee.id AS employee_id
FROM employee JOIN engineer ON engineer.id = employee.id LEFT OUTER JOIN
(employee AS employee_1 JOIN manager AS manager_1 ON manager_1.id =
employee_1.id) ON manager_1.id = employee.manager_id
2
Test 6
SELECT employee.id AS employee_id, engineer.id AS engineer_id
FROM employee JOIN engineer ON engineer.id = employee.id LEFT OUTER JOIN
(employee AS employee_1 JOIN manager AS manager_1 ON manager_1.id =
employee_1.id) ON manager_1.id = employee.manager_id
2
Notice how the outer engineer and employee tables aren't joined. I
would have expected the engineer and employee from the query() call to
be joined.
Example code:
|
#!/usr/bin/env python
fromsqlalchemy importColumn,Integer,String,Enum,__version__
fromsqlalchemy importForeignKey
fromsqlalchemy importcreate_engine
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm importsessionmaker,relationship
print"sqlalchemy version",__version__
engine =create_engine('sqlite:///:memory:',echo=False)
Base=declarative_base()
classEmployee(Base):
__tablename__ ='employee'
id =Column(Integer,primary_key=True)
name =Column(String,index=True,nullable=False)
manager_id =Column(
ForeignKey(
'manager.id',use_alter=True,name="manager_id_fkey"
),index=True,nullable=True)
manager =relationship('Manager',foreign_keys=[manager_id])
employee_type =Column(Enum('manager','engineer',name='employee_type'))
__mapper_args__ ={
'polymorphic_identity':__tablename__,
'polymorphic_on':employee_type
}
classManager(Employee):
__tablename__ ='manager'
id =Column(Integer,ForeignKey('employee.id'),primary_key=True)
__mapper_args__ ={
'polymorphic_identity':__tablename__,
'inherit_condition':id ==Employee.id,
}
classEngineer(Employee):
__tablename__ ='engineer'
id =Column(Integer,ForeignKey('employee.id'),primary_key=True)
__mapper_args__ ={
'polymorphic_identity':__tablename__,
'inherit_condition':id ==Employee.id,
}
Base.metadata.create_all(engine)
Session=sessionmaker(bind=engine)
Session.configure(bind=engine)
session =Session()
the_boss =Manager(name='the boss')
a_manager =Manager(name='a manager',manager=the_boss)
engineer1 =Engineer(name='engineer 1',manager=a_manager)
engineer2 =Engineer(name='engineer 2',manager=a_manager)
session.add(engineer1)
session.add(engineer2)
session.commit()
deftest(test_number,query):
print"\nTest",test_number
printquery
printquery.count()
test(1,session.query(Engineer.id,Employee.id))
test(2,session.query(Employee.id,Engineer.id))
test(3,session.query(Engineer.id).outerjoin(Engineer.manager))
test(4,session.query(Employee.id).outerjoin(Engineer.manager))
test(5,session.query(Engineer.id,Employee.id).outerjoin(Engineer.manager))
test(6,session.query(Employee.id,Engineer.id).outerjoin(Engineer.manager))
|
The output
|
sqlalchemy version 1.0.8
Test1
SELECT engineer.id AS engineer_id,employee.id AS employee_id
FROM employee JOIN engineer ON engineer.id =employee.id
2
Test2
SELECT employee.id AS employee_id,engineer.id AS engineer_id
FROM employee JOIN engineer ON engineer.id =employee.id
2
Test3
SELECT engineer.id AS engineer_id
FROM employee JOIN engineer ON engineer.id =employee.id LEFT OUTER
JOIN (employee AS employee_1 JOIN manager AS manager_1 ON manager_1.id
=employee_1.id)ON manager_1.id =employee.manager_id
2
Test4
SELECT employee.id AS employee_id
FROM employee LEFT OUTER JOIN (employee AS employee_1 JOIN manager AS
manager_1 ON manager_1.id =employee_1.id)ON manager_1.id
=employee.manager_id
4
Test5
SELECT engineer.id AS engineer_id,employee.id AS employee_id
FROM employee JOIN engineer ON engineer.id =employee.id LEFT OUTER
JOIN (employee AS employee_1 JOIN manager AS manager_1 ON manager_1.id
=employee_1.id)ON manager_1.id =employee.manager_id
2
Test6
SELECT employee.id AS employee_id,engineer.id AS engineer_id
FROM engineer,employee LEFT OUTER JOIN (employee AS employee_1 JOIN
manager AS manager_1 ON manager_1.id =employee_1.id)ON manager_1.id
=employee.manager_id
8-
|
This is not an issue, as swapping the columns ends up with the
expected results. However I'd like to understand what's going on.
Thanks for your help
--
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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
