flatten joined-inheritance join targets on individual tables

[Mike Bayer]

Here's a proof of concept that is holding up to the simple cases:

diff -r ab59e3e1135e1c5b59d19a54114163119d5ab1a4 lib/sqlalchemy/orm/mapper.py
--- a/lib/sqlalchemy/orm/mapper.py  Sat Oct 06 12:46:02 2012 -0400
+++ b/lib/sqlalchemy/orm/mapper.py  Mon Oct 08 10:48:53 2012 -0400
@@ -112,6 +112,7 @@
                  exclude_properties=None,
                  passive_updates=True,
                  eager_defaults=False,
+                 flatten_joined=False,
                  _compiled_cache_size=100,
                  ):
         """Construct a new mapper.
@@ -160,7 +161,7 @@
         self._compiled_cache_size = _compiled_cache_size
         self._reconstructor = None
         self._deprecated_extensions = util.to_list(extension or [       self.flatten_joined = flatten_joined
         self.allow_partial_pks = allow_partial_pks

         self._set_with_polymorphic(with_polymorphic)
@@ -516,7 +517,8 @@
                     self.mapped_table = sql.join(
                                                 self.inherits.mapped_table,
                                                 self.local_table,
-                                                self.inherit_condition)
+                                                self.inherit_condition,
+                                                flat_alias=self.flatten_joined)

                     fks = util.to_set(self.inherit_foreign_keys)
                     self._inherits_equated_pairs = sql_util.criterion_as_pairs(
diff -r ab59e3e1135e1c5b59d19a54114163119d5ab1a4 lib/sqlalchemy/sql/expression.py
--- a/lib/sqlalchemy/sql/expression.py  Sat Oct 06 12:46:02 2012 -0400
+++ b/lib/sqlalchemy/sql/expression.py  Mon Oct 08 10:48:53 2012 -0400
@@ -113,7 +113,7 @@
     """
     return UnaryExpression(column, modifier=operators.asc_op)

-def outerjoin(left, right, onclause=None):
+def outerjoin(left, right, onclause=None, **kw):
     """Return an ``OUTER JOIN`` clause element.

     The returned object is an instance of :class:`.Join`.
@@ -135,9 +135,9 @@
     :class:`.Join` object.

     """
-    return Join(left, right, onclause, isouter=True)
-
-def join(left, right, onclause=None, isouter=False):
+    return Join(left, right, onclause, isouter=True, **kw)
+
+def join(left, right, onclause=None, isouter=False, **kw):
     """Return a ``JOIN`` clause element (regular inner join).

     The returned object is an instance of :class:`.Join`.
@@ -160,7 +160,7 @@


     """
-    return Join(left, right, onclause, isouter)
+    return Join(left, right, onclause, isouter, **kw)

 def select(columns=None, whereclause=None, from_obj=[](])
-
+), **kwargs):
     """Returns a ``SELECT`` clause element.
@@ -3772,7 +3772,7 @@
     """
     __visit_name__ = 'join'

-    def __init__(self, left, right, onclause=None, isouter=False):
+    def __init__(self, left, right, onclause=None, isouter=False, flat_alias=False):
         """Construct a new :class:`.Join`.

         The usual entrypoint here is the :func:`~.expression.join`
@@ -3789,6 +3789,7 @@
             self.onclause = onclause

         self.isouter = isouter
+        self.flat_alias = flat_alias

     @property
     def description(self):
@@ -3899,7 +3900,14 @@
         aliases.

         """
-        return self.select(use_labels=True, correlate=False).alias(name)
+        if self.flat_alias:
+            la = self.left.alias()
+            ra = self.right.alias()
+            onclause = sqlutil.ClauseAdapter(la).traverse(self.onclause)
+            onclause = sqlutil.ClauseAdapter(ra).traverse(onclause)
+            return join(la, ra, onclause)
+        else:
+            return self.select(use_labels=True, correlate=False).alias(name)

     @property
     def _hide_froms(self):

a sampling of standard usages seems to work with a supporting database:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    bs = relationship("B")


class B(A):
    __tablename__ = "b"

    id = Column(Integer, ForeignKey('a'), primary_key=True)
    c_id = Column(Integer, ForeignKey('c.id'))

    __mapper_args__ = {'flatten_joined': True}

class C(Base):
    __tablename__ = "c"

    id = Column(Integer, primary_key=True)
    bs = relationship(B, lazy="joined")

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)

s.add_all([   C(bs=[B(), B(), B()](
)),
    C(bs=[B()](B(),))
])
s.commit()

for c in s.query(C):
    print c.bs

print s.query(C).join(C.bs).filter(B.id < 3).all()


b_1 = aliased(B)
b_2 = aliased(B)

print s.query(b_1, b_2).select_from(b_1).join(b_2, b_1.id > b_2.id).all()

considering the "alias" of a joined-inh as an alias of the component tables seems to work pretty well. this might even be a decent "experimental" feature in 0.8.

SELECT c.id AS c_id, b_1.id AS b_1_id, a_1.id AS a_1_id, b_1.c_id AS b_1_c_id 
FROM c LEFT OUTER JOIN (a AS a_1 JOIN b AS b_1 ON a_1.id = b_1.id) ON c.id = b_1.c_id

SELECT c.id AS c_id, b_1.id AS b_1_id, a_1.id AS a_1_id, b_1.c_id AS b_1_c_id 
FROM c JOIN (a AS a_2 JOIN b AS b_2 ON a_2.id = b_2.id) ON c.id = b_2.c_id LEFT OUTER JOIN (a AS a_1 JOIN b AS b_1 ON a_1.id = b_1.id) ON c.id = b_1.c_id 
WHERE b_2.id < %(id_1)s
{'id_1': 3}

SELECT b_1.id AS b_1_id, a_1.id AS a_1_id, b_1.c_id AS b_1_c_id, b_2.id AS b_2_id, a_2.id AS a_2_id, b_2.c_id AS b_2_c_id 
FROM a AS a_1 JOIN b AS b_1 ON a_1.id = b_1.id JOIN (a AS a_2 JOIN b AS b_2 ON a_2.id = b_2.id) ON b_1.id > b_2.id
{}

[Mike Bayer]

lots of tests pass in test_polymorphic_rel, though as expected some of the hard ones like nested subquery loading fail.

[Mike Bayer]

yeah, easy to make simple cases work, but a string of joins as performed by subqueryloading has major issues.

[Mike Bayer]

another thought. push it into the compiler (copied from #2369, applies more closely here).

# nested join:

SELECT 
    a.x AS a_x, a.y AS a_y, 
    b.x AS b_x, b.y AS b_y, 
    c.x AS c_x, c.y AS c_y
FROM a JOIN (
        b JOIN c on b.id=c.id
    ) on a.id=b.id

# currently, ORM would rewrite this as:

SELECT 
    a.x AS a_x, a.y AS a_y, 
    anon_1.b_x AS anon_1_b_x, 
    anon_1.b_y AS anon_1_b_y, 
    anon_1.c_x AS anon_1_c_x, 
    anon_1.c_y AS anon_1_c_y

FROM a JOIN (
        SELECT b.id AS b_id, b.x AS b_x, b.y AS b_y, c.x AS c_x, c.y AS c_y 
        FROM b JOIN c on b.id=c.id
    ) AS anon_1 on a.id=anon_1.b_id


# but if we could get compiler to rewrite as:

SELECT 
    a.x AS a_x, a.y AS a_y, 
    anon_1.b_x AS b_x, 
    anon_1.b_y AS b_y, 
    anon_1.c_x AS c_x, 
    anon_1.c_y AS c_y
FROM a JOIN (
        SELECT b.id AS b_id, b.x AS b_x, b.y AS b_y, c.x AS c_x, c.y AS c_y 
        FROM b JOIN c on b.id=c.id
    ) AS anon_1 on a.id=anon_1.b_id

[Mike Bayer]

also:

when we join from A to a mapper "B join C" and we are aliasing, as in eager loading:

orm_join(A, BC.alias())

SELECT a.id AS a_id, anon_1.b_id AS anon_1_b_id, anon_1.c_id AS anon_1_c_id
FROM a JOIN (
   SELECT b.id AS b_id, c.id AS c_id FROM
   b JOIN c ON b.id=c.id
) AS anon_1 ON a.id=anon_1.b_id

we can do the "flattened" thing, where we don't create the subquery, but again produce the same labels:

orm_join(A, BC.unnessted_alias())

SELECT a.id AS a_id, anon_1_b.id AS anon_1_b_id, anon_1_c.id AS anon_1_c_id
FROM a JOIN (b AS anon_1_b JOIN c AS anon_1_c ON anon_1_b.id=anon_1_c.id)
ON a.id=anon_1_b_id

again, we get the same labeling conventions and everything, we here just change how Alias represents its contents. Perhaps JoinedAlias subclass.

The above query, when run through the previous compiler "subquery the nested joins" converter, gets:

SELECT a.id AS a_id, anon_2.anon_1_b_id AS anon_1_b_id, anon_2.anon_1_c_id AS anon_1_c_id
FROM a JOIN (
     SELECT anon_1_b.id AS anon_1_b_id, anon_1_c.id AS anon_1_c_id
     FROM b AS anon_1_b JOIN c AS anon_1_c ON anon_1_b.id=anon_1_c.id
) AS anon_2
ON a.id=anon_2.anon_1_b_id

and it works

[Mike Bayer]

that unnested call goes right here:

diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py
index ddc0dd8..3e5a607 100644
--- a/lib/sqlalchemy/orm/util.py
+++ b/lib/sqlalchemy/orm/util.py
@@ -501,7 +501,7 @@ class AliasedClass(object):
                             use_mapper_path=False):
         mapper = _class_to_mapper(cls)
         if alias is None:
-            alias = mapper._with_polymorphic_selectable.alias(name=name)
+            alias = mapper._with_polymorphic_selectable.unnested_alias(name=name)
         self._aliased_insp = AliasedInsp(
             self,
             mapper,

[Mike Bayer]

how to make query(A).join(A.joinedinh) work:

diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 79fd61c..3d7f376 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -1871,10 +1871,7 @@ class Query(object):
         aliased_entity = right_mapper and \
                             not right_is_aliased and \
                             (
-                                right_mapper.with_polymorphic or
-                                isinstance(
-                                    right_mapper.mapped_table,
-                                    expression.Join)
+                                right_mapper.with_polymorphic
                             )

         if not need_adapter and (create_aliases or aliased_entity):
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 5820cb1..b95cf14 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -4215,7 +4215,8 @@ class FromGrouping(FromClause):
         # this could be
         # self.element.foreign_keys
         # see SelectableTest.test_join_condition
-        return set()
+        return self.element.foreign_keys
+#        return set()

     @property
     def _hide_froms(self):

[Mike Bayer]

most of how this has proceeded can be seen in diff:@32716eae773e6f6b7f37baf705342c1ed89df461:69e9574fefd5fbb4673c99ad476a00b03fe22318 , still some cleanup to go + docs.

[Mike Bayer]

it's as good as it's going to get. Also committed some tests/tweaks for Oracle 8.

[Mike Bayer]

Removing milestone: 0.9.0 (automated comment)