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
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
from sqlalchemy import Column, Integer, String, Enum, __version__
from sqlalchemy import ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
print "sqlalchemy version", __version__
engine = create_engine('sqlite:///:memory:', echo=False)
Base = declarative_base()
class Employee(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
}
class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity': __tablename__,
'inherit_condition': id == Employee.id,
}
class Engineer(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()
def test(test_number, query):
print "\nTest", test_number
print query
print query.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
Test 1
SELECT engineer.id AS engineer_id, employee.id AS employee_id
FROM employee JOIN engineer ON engineer.id = employee.id
2
Test 2
SELECT employee.id AS employee_id, engineer.id AS engineer_id
FROM employee JOIN engineer ON engineer.id = employee.id
2
Test 3
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
Test 4
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
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 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
