[Sqlalchemy-commits] sqlalchemy: - [bug] Calls to query.join() to a single-table

[<co...@sq...>]

details:   http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/0488f1bf3e19
changeset: 7940:0488f1bf3e19
user:      zzzeek
date:      Sat Dec 03 20:57:27 2011 -0500
description:
- [bug] Calls to query.join() to a single-table
inheritance subclass are now tracked, and
are used to eliminate the additional WHERE..
IN criterion normally tacked on with single
table inheritance, since the join should
accommodate it.  This allows OUTER JOIN
to a single table subclass to produce
the correct results, and overall will produce
fewer WHERE criterion when dealing with
single table inheritance joins.
[ticket:2328]

diffstat:

 CHANGES                             |  12 ++++++++
 lib/sqlalchemy/orm/query.py         |   7 ++++-
 test/orm/inheritance/test_single.py |  51 +++++++++++++++++++++++++++++++++++-
 3 files changed, 67 insertions(+), 3 deletions(-)

diffs (130 lines):

diff -r e572bc5ae9ee -r 0488f1bf3e19 CHANGES
--- a/CHANGES	Sat Dec 03 20:09:27 2011 -0500
+++ b/CHANGES	Sat Dec 03 20:57:27 2011 -0500
@@ -84,6 +84,18 @@
      a tuple is inadvertently passed to session.query()
      [ticket:2297].  Also in 0.6.9.
 
+   - [bug] Calls to query.join() to a single-table
+     inheritance subclass are now tracked, and
+     are used to eliminate the additional WHERE..
+     IN criterion normally tacked on with single
+     table inheritance, since the join should
+     accommodate it.  This allows OUTER JOIN
+     to a single table subclass to produce
+     the correct results, and overall will produce
+     fewer WHERE criterion when dealing with 
+     single table inheritance joins.  
+     [ticket:2328]
+
 - sql
    - [bug] related to [ticket:2316], made some 
      adjustments to the change from [ticket:2261]
diff -r e572bc5ae9ee -r 0488f1bf3e19 lib/sqlalchemy/orm/query.py
--- a/lib/sqlalchemy/orm/query.py	Sat Dec 03 20:09:27 2011 -0500
+++ b/lib/sqlalchemy/orm/query.py	Sat Dec 03 20:57:27 2011 -0500
@@ -89,6 +89,7 @@
     _only_load_props = None
     _refresh_state = None
     _from_obj = ()
+    _join_entities = ()
     _select_from_entity = None
     _filter_aliases = None
     _from_obj_alias = None
@@ -1641,6 +1642,9 @@
         left_mapper, left_selectable, left_is_aliased = _entity_info(left)
         right_mapper, right_selectable, right_is_aliased = _entity_info(right)
 
+        if right_mapper:
+            self._join_entities += (right, )
+
         if right_mapper and prop and \
                 not right_mapper.common_parent(prop.mapper):
             raise sa_exc.InvalidRequestError(
@@ -2818,9 +2822,10 @@
         selected from the total results.
 
         """
-
         for entity, (mapper, adapter, s, i, w) in \
                             self._mapper_adapter_map.iteritems():
+            if entity in self._join_entities:
+                continue
             single_crit = mapper._single_table_criterion
             if single_crit is not None:
                 if adapter:
diff -r e572bc5ae9ee -r 0488f1bf3e19 test/orm/inheritance/test_single.py
--- a/test/orm/inheritance/test_single.py	Sat Dec 03 20:09:27 2011 -0500
+++ b/test/orm/inheritance/test_single.py	Sat Dec 03 20:57:27 2011 -0500
@@ -295,7 +295,9 @@
                             use_default_dialect=True
                             )
 
-class RelationshipToSingleTest(fixtures.MappedTest):
+class RelationshipToSingleTest(testing.AssertsCompiledSQL, fixtures.MappedTest):
+    __dialect__ = 'default'
+
     @classmethod
     def define_tables(cls, metadata):
         Table('employees', metadata,
@@ -327,7 +329,8 @@
             pass
 
     def test_of_type(self):
-        JuniorEngineer, Company, companies, Manager, Employee, employees, Engineer = (self.classes.JuniorEngineer,
+        JuniorEngineer, Company, companies, Manager,\
+                Employee, employees, Engineer = (self.classes.JuniorEngineer,
                                 self.classes.Company,
                                 self.tables.companies,
                                 self.classes.Manager,
@@ -368,6 +371,50 @@
             ]
         )
 
+    def test_outer_join(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.name).outerjoin("engineers"),
+            "SELECT companies.company_id AS companies_company_id, "
+            "companies.name AS companies_name, employees.name AS employees_name "
+            "FROM companies LEFT OUTER JOIN employees ON companies.company_id "
+            "= employees.company_id AND employees.type IN (:type_1)" 
+        )
+
+    def test_outer_join_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.name).outerjoin(eng_alias, Company.engineers),
+            "SELECT companies.company_id AS companies_company_id, "
+            "companies.name AS companies_name, employees_1.name AS "
+            "employees_1_name 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,