From dd6389f171736abd28d777b6fde40403cab0c13e Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 9 Oct 2014 20:40:35 -0400 Subject: [PATCH] - The ON clause rendered when using :meth:`.Query.join`, :meth:`.Query.outerjoin`, or the standalone :func:`.orm.join` / :func:`.orm.outerjoin` functions to a single-inheritance subclass will now include the "single table criteria" in the ON clause even if the ON clause is otherwise hand-rolled; it is now added to the criteria using AND, the same way as if joining to a single-table target using relationship or similar. fixes #3222 --- doc/build/changelog/changelog_10.rst | 14 +++ lib/sqlalchemy/orm/util.py | 10 +++ test/orm/inheritance/test_single.py | 122 ++++++++++++++++++++++++++- 3 files changed, 144 insertions(+), 2 deletions(-) diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst index 69b5b29c1e..60dea05649 100644 --- a/doc/build/changelog/changelog_10.rst +++ b/doc/build/changelog/changelog_10.rst @@ -21,6 +21,20 @@ series as well. For changes that are specific to 1.0 with an emphasis on compatibility concerns, see :doc:`/changelog/migration_10`. + .. change:: + :tags: bug, orm + :tickets: 3222 + + The ON clause rendered when using :meth:`.Query.join`, + :meth:`.Query.outerjoin`, or the standalone :func:`.orm.join` / + :func:`.orm.outerjoin` functions to a single-inheritance subclass will + now include the "single table criteria" in the ON clause even + if the ON clause is otherwise hand-rolled; it is now added to the + criteria using AND, the same way as if joining to a single-table + target using relationship or similar. + + This is sort of in-between feature and bug. + .. change:: :tags: feature, sql :tickets: 3184 diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py index 734f9d5e6f..8d40ae21c9 100644 --- a/lib/sqlalchemy/orm/util.py +++ b/lib/sqlalchemy/orm/util.py @@ -804,6 +804,16 @@ class _ORMJoin(expression.Join): expression.Join.__init__(self, left, right, onclause, isouter) + if not prop and getattr(right_info, 'mapper', None) \ + and right_info.mapper.single: + # if single inheritance target and we are using a manual + # or implicit ON clause, augment it the same way we'd augment the + # WHERE. + single_crit = right_info.mapper._single_table_criterion + if right_info.is_aliased_class: + single_crit = right_info._adapter.traverse(single_crit) + self.onclause = self.onclause & single_crit + def join(self, right, onclause=None, isouter=False, join_to_left=None): return _ORMJoin(self, right, onclause, isouter) diff --git a/test/orm/inheritance/test_single.py b/test/orm/inheritance/test_single.py index be42cce521..6112929b63 100644 --- a/test/orm/inheritance/test_single.py +++ b/test/orm/inheritance/test_single.py @@ -386,7 +386,7 @@ class RelationshipToSingleTest(testing.AssertsCompiledSQL, fixtures.MappedTest): ] ) - def test_outer_join(self): + def test_outer_join_prop(self): Company, Employee, Engineer = self.classes.Company,\ self.classes.Employee,\ self.classes.Engineer @@ -407,7 +407,7 @@ class RelationshipToSingleTest(testing.AssertsCompiledSQL, fixtures.MappedTest): "= employees.company_id AND employees.type IN (:type_1)" ) - def test_outer_join_alias(self): + def test_outer_join_prop_alias(self): Company, Employee, Engineer = self.classes.Company,\ self.classes.Employee,\ self.classes.Engineer @@ -431,6 +431,124 @@ class RelationshipToSingleTest(testing.AssertsCompiledSQL, fixtures.MappedTest): ) + def test_outer_join_literal_onclause(self): + Company, Employee, Engineer = self.classes.Company,\ + self.classes.Employee,\ + self.classes.Engineer + companies, employees = self.tables.companies, self.tables.employees + + mapper(Company, companies, properties={ + 'engineers':relationship(Engineer) + }) + mapper(Employee, employees, polymorphic_on=employees.c.type) + mapper(Engineer, inherits=Employee, polymorphic_identity='engineer') + + sess = create_session() + self.assert_compile( + sess.query(Company, Engineer).outerjoin( + Engineer, Company.company_id == Engineer.company_id), + "SELECT companies.company_id AS companies_company_id, " + "companies.name AS companies_name, " + "employees.employee_id AS employees_employee_id, " + "employees.name AS employees_name, " + "employees.manager_data AS employees_manager_data, " + "employees.engineer_info AS employees_engineer_info, " + "employees.type AS employees_type, " + "employees.company_id AS employees_company_id FROM companies " + "LEFT OUTER JOIN employees ON " + "companies.company_id = employees.company_id " + "AND employees.type IN (:type_1)" + ) + + def test_outer_join_literal_onclause_alias(self): + Company, Employee, Engineer = self.classes.Company,\ + self.classes.Employee,\ + self.classes.Engineer + companies, employees = self.tables.companies, self.tables.employees + + mapper(Company, companies, properties={ + 'engineers':relationship(Engineer) + }) + mapper(Employee, employees, polymorphic_on=employees.c.type) + mapper(Engineer, inherits=Employee, polymorphic_identity='engineer') + + eng_alias = aliased(Engineer) + sess = create_session() + self.assert_compile( + sess.query(Company, eng_alias).outerjoin( + eng_alias, Company.company_id == eng_alias.company_id), + "SELECT companies.company_id AS companies_company_id, " + "companies.name AS companies_name, " + "employees_1.employee_id AS employees_1_employee_id, " + "employees_1.name AS employees_1_name, " + "employees_1.manager_data AS employees_1_manager_data, " + "employees_1.engineer_info AS employees_1_engineer_info, " + "employees_1.type AS employees_1_type, " + "employees_1.company_id AS employees_1_company_id " + "FROM companies LEFT OUTER JOIN employees AS employees_1 ON " + "companies.company_id = employees_1.company_id " + "AND employees_1.type IN (:type_1)" + ) + + def test_outer_join_no_onclause(self): + Company, Employee, Engineer = self.classes.Company,\ + self.classes.Employee,\ + self.classes.Engineer + companies, employees = self.tables.companies, self.tables.employees + + mapper(Company, companies, properties={ + 'engineers':relationship(Engineer) + }) + mapper(Employee, employees, polymorphic_on=employees.c.type) + mapper(Engineer, inherits=Employee, polymorphic_identity='engineer') + + sess = create_session() + self.assert_compile( + sess.query(Company, Engineer).outerjoin( + Engineer), + "SELECT companies.company_id AS companies_company_id, " + "companies.name AS companies_name, " + "employees.employee_id AS employees_employee_id, " + "employees.name AS employees_name, " + "employees.manager_data AS employees_manager_data, " + "employees.engineer_info AS employees_engineer_info, " + "employees.type AS employees_type, " + "employees.company_id AS employees_company_id " + "FROM companies LEFT OUTER JOIN employees ON " + "companies.company_id = employees.company_id " + "AND employees.type IN (:type_1)" + ) + + def test_outer_join_no_onclause_alias(self): + Company, Employee, Engineer = self.classes.Company,\ + self.classes.Employee,\ + self.classes.Engineer + companies, employees = self.tables.companies, self.tables.employees + + mapper(Company, companies, properties={ + 'engineers':relationship(Engineer) + }) + mapper(Employee, employees, polymorphic_on=employees.c.type) + mapper(Engineer, inherits=Employee, polymorphic_identity='engineer') + + eng_alias = aliased(Engineer) + sess = create_session() + self.assert_compile( + sess.query(Company, eng_alias).outerjoin( + eng_alias), + "SELECT companies.company_id AS companies_company_id, " + "companies.name AS companies_name, " + "employees_1.employee_id AS employees_1_employee_id, " + "employees_1.name AS employees_1_name, " + "employees_1.manager_data AS employees_1_manager_data, " + "employees_1.engineer_info AS employees_1_engineer_info, " + "employees_1.type AS employees_1_type, " + "employees_1.company_id AS employees_1_company_id " + "FROM companies LEFT OUTER JOIN employees AS employees_1 ON " + "companies.company_id = employees_1.company_id " + "AND employees_1.type IN (:type_1)" + ) + def test_relationship_to_subclass(self): JuniorEngineer, Company, companies, Manager, \ Employee, employees, Engineer = (self.classes.JuniorEngineer, -- 2.47.2