- tests for the alias() API

- docs docs docs

4 files changed, 379 insertions, 42 deletions

diff --git a/doc/build/changelog/migration_09.rst b/doc/build/changelog/migration_09.rst
index 03c84d8d9..e651b08d4 100644
--- a/doc/build/changelog/migration_09.rst
+++ b/doc/build/changelog/migration_09.rst
@@ -41,6 +41,165 @@ At the moment, the C extensions are still not fully ported to
 Python 3.
 
 
+New ORM Features
+================
+
+.. _feature_joins_09:
+
+Many JOIN and LEFT OUTER JOIN expressions will no longer be wrapped in (SELECT * FROM ..) AS ANON_1
+---------------------------------------------------------------------------------------------------
+
+For many years, the SQLAlchemy ORM has been held back from being able to nest
+a JOIN inside the right side of an existing JOIN::
+
+    SELECT a.*, b.*, c.* FROM a JOIN (b JOIN c ON b.id = c.id) ON a.id
+
+This was due to the fact that SQLite, even today, cannot parse a statement of the above format::
+
+    SQLite version 3.7.15.2 2013-01-09 11:53:05
+    Enter ".help" for instructions
+    Enter SQL statements terminated with a ";"
+    sqlite> create table a(id integer);
+    sqlite> create table b(id integer);
+    sqlite> create table c(id integer);
+    sqlite> select a.id, b.id, c.id from a join (b join c on b.id=c.id) on b.id=a.id;
+    Error: no such column: b.id
+    sqlite>
+
+Back in 2005, it's possible, though not confirmed, that maybe older versions of MySQL
+(like in the 4 series) or Oracle (perhaps in the 8 or 9 series) also couldn't handle the above form;
+though today, every database tested except SQLite now supports it.   To make matters worse,
+SQLAlchemy's usual workaround of applying a SELECT often degrades performance on platforms
+like Postgresql and MySQL::
+
+    SELECT a.*, anon_1.* 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
+
+A JOIN like the above form is commonplace when working with joined-table inheritance structures;
+any time :meth:`.Query.join` is used to join from some parent to a joined-table subclass, or
+when :func:`.joinedload` is used similarly, SQLAlchemy's ORM would always make sure a nested
+JOIN was never rendered, lest the query wouldn't be able to run on SQLite.  Even though
+the Core has always supported a JOIN of the more compact form, the ORM had to avoid it.
+
+An additional issue would arise when producing joins across many-to-many relationships
+where special criteria is present in the ON clause. Consider an eager load join like the following::
+
+    session.query(Order).outerjoin(Order.items)
+
+Assuming a many-to-many from ``Order`` to ``Item`` which actually refers to a subclass
+like ``Subitem``, the SQL for the above would look like::
+
+    SELECT order.id, order.name
+    FROM order LEFT OUTER JOIN order_item ON order.id = order_item.order_id
+    LEFT OUTER JOIN item ON order_item.item_id = item.id AND item.type = 'subitem'
+
+What's wrong with the above query?  Basically, that it will load many ``order`` /
+``order_item`` rows where the criteria of ``item.type == 'subitem'`` is not true.
+
+As of SQLAlchemy 0.9, an entirely new approach has been taken.  The ORM no longer
+worries about nesting JOINs in the right side of an enclosing JOIN, and it now will
+render these as often as possible while still returning the correct results.  When
+the SQL statement is passed to be compiled, the **dialect compiler** will **rewrite the join**
+to suit the target backend, if that backend is known to not support a right-nested
+JOIN (which currently is only SQLite - if other backends have this issue please
+let us know!).
+
+So a regular ``query(Parent).join(Subclass)`` will now usually produce a simpler
+expression::
+
+    SELECT parent.id AS parent_id
+    FROM parent JOIN (
+            base_table JOIN subclass_table
+            ON base_table.id = subclass_table.id) ON parent.id = base_table.parent_id
+
+Joined eager loads like ``query(Parent).options(joinedload(Parent.subclasses))``
+will alias the individual tables instead of wrapping in an ``ANON_1``::
+
+    SELECT parent.*, base_table_1.*, subclass_table_1.* FROM parent
+        LEFT OUTER JOIN (
+            base_table AS base_table_1 JOIN subclass_table AS subclass_table_1
+            ON base_table_1.id = subclass_table_1.id)
+            ON parent.id = base_table_1.parent_id
+
+Many-to-many joins and eagerloads will right nest the "secondary" and "right" tables::
+
+    SELECT order.id, order.name
+    FROM order LEFT OUTER JOIN
+    (order_item JOIN item ON order_item.item_id = item.id AND item.type = 'subitem')
+    ON order_item.order_id = order.id
+
+All of these joins, when rendered with a :class:`.Select` statement that specifically
+specifies ``use_labels=True``, which is true for all the queries the ORM emits,
+are candidates for "join rewriting", which is the process of rewriting all those right-nested
+joins into nested SELECT statements, while maintaining the identical labeling used by
+the :class:`.Select`.  So SQLite, the one database that won't support this very
+common SQL syntax even in 2013, shoulders the extra complexity itself,
+with the above queries rewritten as::
+
+    -- sqlite only!
+    SELECT parent.id AS parent_id
+        FROM parent JOIN (
+            SELECT base_table.id AS base_table_id,
+                    base_table.parent_id AS base_table_parent_id,
+                    subclass_table.id AS subclass_table_id
+            FROM base_table JOIN subclass_table ON base_table.id = subclass_table.id
+        ) AS anon_1 ON parent.id = anon_1.base_table_parent_id
+
+    -- sqlite only!
+    SELECT parent.id AS parent_id, anon_1.subclass_table_1_id AS subclass_table_1_id,
+            anon_1.base_table_1_id AS base_table_1_id,
+            anon_1.base_table_1_parent_id AS base_table_1_parent_id
+    FROM parent LEFT OUTER JOIN (
+        SELECT base_table_1.id AS base_table_1_id,
+            base_table_1.parent_id AS base_table_1_parent_id,
+            subclass_table_1.id AS subclass_table_1_id
+        FROM base_table AS base_table_1
+        JOIN subclass_table AS subclass_table_1 ON base_table_1.id = subclass_table_1.id
+    ) AS anon_1 ON parent.id = anon_1.base_table_1_parent_id
+
+    -- sqlite only!
+    SELECT "order".id AS order_id
+    FROM "order" LEFT OUTER JOIN (
+            SELECT order_item_1.order_id AS order_item_1_order_id,
+                order_item_1.item_id AS order_item_1_item_id,
+                item.id AS item_id, item.type AS item_type
+    FROM order_item AS order_item_1
+        JOIN item ON item.id = order_item_1.item_id AND item.type IN (?)
+    ) AS anon_1 ON "order".id = anon_1.order_item_1_order_id
+
+The :meth:`.Join.alias`, :func:`.aliased` and :func:`.with_polymorphic` functions now
+support a new argument, ``flat=True``, which is used to construct aliases of joined-table
+entities without embedding into a SELECT.   This flag is not on by default, to help with
+backwards compatibility - but now a "polymorhpic" selectable can be joined as a target
+without any subqueries generated::
+
+    employee_alias = with_polymorphic(Person, [Engineer, Manager], flat=True)
+
+    session.query(Company).join(
+                        Company.employees.of_type(employee_alias)
+                    ).filter(
+                        or_(
+                            Engineer.priamry_language == 'python',
+                            Manager.golf_swing == 'fore'
+                        )
+                    )
+
+Generates (everywhere except SQLite)::
+
+    SELECT companies.company_id AS companies_company_id, companies.name AS companies_name
+    FROM companies JOIN (
+        people AS people_1
+        LEFT OUTER JOIN engineers AS engineers_1 ON people_1.person_id = engineers_1.person_id
+        LEFT OUTER JOIN managers AS managers_1 ON people_1.person_id = managers_1.person_id
+    ) ON companies.company_id = people_1.company_id
+    WHERE engineers.primary_language = %(primary_language_1)s
+        OR managers.manager_name = %(manager_name_1)s
+
+:ticket:`2369` :ticket:`2587`
+
+
 .. _behavioral_changes_09:
 
 Behavioral Changes
@@ -58,39 +217,39 @@ render.
 
 Consider the following example against the usual ``User`` mapping::
 
-	select_stmt = select([User]).where(User.id == 7).alias()
+    select_stmt = select([User]).where(User.id == 7).alias()
 
-	q = session.query(User).\
+    q = session.query(User).\
                join(select_stmt, User.id == select_stmt.c.id).\
                filter(User.name == 'ed')
 
 The above statement predictably renders SQL like the following::
 
-	SELECT "user".id AS user_id, "user".name AS user_name
-	FROM "user" JOIN (SELECT "user".id AS id, "user".name AS name
-	FROM "user"
-	WHERE "user".id = :id_1) AS anon_1 ON "user".id = anon_1.id
-	WHERE "user".name = :name_1
+    SELECT "user".id AS user_id, "user".name AS user_name
+    FROM "user" JOIN (SELECT "user".id AS id, "user".name AS name
+    FROM "user"
+    WHERE "user".id = :id_1) AS anon_1 ON "user".id = anon_1.id
+    WHERE "user".name = :name_1
 
 If we wanted to reverse the order of the left and right elements of the
 JOIN, the documentation would lead us to believe we could use
 :meth:`.Query.select_from` to do so::
 
-	q = session.query(User).\
-	        select_from(select_stmt).\
-	        join(User, User.id == select_stmt.c.id).\
-	        filter(User.name == 'ed')
+    q = session.query(User).\
+            select_from(select_stmt).\
+            join(User, User.id == select_stmt.c.id).\
+            filter(User.name == 'ed')
 
 However, in version 0.8 and earlier, the above use of :meth:`.Query.select_from`
 would apply the ``select_stmt`` to **replace** the ``User`` entity, as it
 selects from the ``user`` table which is compatible with ``User``::
 
-	-- SQLAlchemy 0.8 and earlier...
-	SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
-	FROM (SELECT "user".id AS id, "user".name AS name
-	FROM "user"
-	WHERE "user".id = :id_1) AS anon_1 JOIN "user" ON anon_1.id = anon_1.id
-	WHERE anon_1.name = :name_1
+    -- SQLAlchemy 0.8 and earlier...
+    SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
+    FROM (SELECT "user".id AS id, "user".name AS name
+    FROM "user"
+    WHERE "user".id = :id_1) AS anon_1 JOIN "user" ON anon_1.id = anon_1.id
+    WHERE anon_1.name = :name_1
 
 The above statement is a mess, the ON clause refers ``anon_1.id = anon_1.id``,
 our WHERE clause has been replaced with ``anon_1`` as well.
@@ -101,10 +260,10 @@ is now available by a new method known as :meth:`.Query.select_entity_from`.
 This is a lesser used behavior that in modern SQLAlchemy is roughly equivalent
 to selecting from a customized :func:`.aliased` construct::
 
-	select_stmt = select([User]).where(User.id == 7)
-	user_from_stmt = aliased(User, select_stmt.alias())
+    select_stmt = select([User]).where(User.id == 7)
+    user_from_stmt = aliased(User, select_stmt.alias())
 
-	q = session.query(user_from_stmt).filter(user_from_stmt.name == 'ed')
+    q = session.query(user_from_stmt).filter(user_from_stmt.name == 'ed')
 
 So with SQLAlchemy 0.9, our query that selects from ``select_stmt`` produces
 the SQL we expect::
@@ -140,24 +299,24 @@ reports support of this feature.
 
 E.g. an example like::
 
-	from sqlalchemy.sql import table, column, select, func
+    from sqlalchemy.sql import table, column, select, func
 
-	t = table('t', column('c1'), column('c2'))
-	expr = (func.foo(t.c.c1) + t.c.c2).label("expr")
+    t = table('t', column('c1'), column('c2'))
+    expr = (func.foo(t.c.c1) + t.c.c2).label("expr")
 
-	stmt = select([expr]).order_by(expr)
+    stmt = select([expr]).order_by(expr)
 
-	print stmt
+    print stmt
 
 Prior to 0.9 would render as::
 
-	SELECT foo(t.c1) + t.c2 AS expr
-	FROM t ORDER BY foo(t.c1) + t.c2
+    SELECT foo(t.c1) + t.c2 AS expr
+    FROM t ORDER BY foo(t.c1) + t.c2
 
-And now	renders as::
+And now renders as::
 
-	SELECT foo(t.c1) + t.c2 AS expr
-	FROM t ORDER BY expr
+    SELECT foo(t.c1) + t.c2 AS expr
+    FROM t ORDER BY expr
 
 The ORDER BY only renders the label if the label isn't further embedded into an expression within the ORDER BY, other than a simple ``ASC`` or ``DESC``.
 
diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py
index fb6471ac6..38cf58792 100644
--- a/lib/sqlalchemy/orm/util.py
+++ b/lib/sqlalchemy/orm/util.py
@@ -730,15 +730,28 @@ def aliased(element, alias=None, name=None, flat=False, adapt_on_names=False):
 
     :param element: element to be aliased.  Is normally a mapped class,
      but for convenience can also be a :class:`.FromClause` element.
+
     :param alias: Optional selectable unit to map the element to.  This should
      normally be a :class:`.Alias` object corresponding to the :class:`.Table`
      to which the class is mapped, or to a :func:`.select` construct that
      is compatible with the mapping.   By default, a simple anonymous
      alias of the mapped table is generated.
+
     :param name: optional string name to use for the alias, if not specified
      by the ``alias`` parameter.  The name, among other things, forms the
      attribute name that will be accessible via tuples returned by a
      :class:`.Query` object.
+
+    :param flat: Boolean, will be passed through to the :meth:`.FromClause.alias`
+     call so that aliases of :class:`.Join` objects don't include an enclosing
+     SELECT.  This can lead to more efficient queries in many circumstances.
+     A JOIN against a nested JOIN will be rewritten as a JOIN against an aliased
+     SELECT subquery on backends that don't support this syntax.
+
+     .. versionadded:: 0.9.0
+
+     .. seealso:: :meth:`.Join.alias`
+
     :param adapt_on_names: if True, more liberal "matching" will be used when
      mapping the mapped columns of the ORM entity to those of the
      given selectable - a name-based match will be performed if the
@@ -816,6 +829,19 @@ def with_polymorphic(base, classes, selectable=False,
         support parenthesized joins, such as SQLite and older
         versions of MySQL.
 
+    :param flat: Boolean, will be passed through to the :meth:`.FromClause.alias`
+     call so that aliases of :class:`.Join` objects don't include an enclosing
+     SELECT.  This can lead to more efficient queries in many circumstances.
+     A JOIN against a nested JOIN will be rewritten as a JOIN against an aliased
+     SELECT subquery on backends that don't support this syntax.
+
+     Setting ``flat`` to ``True`` implies the ``aliased`` flag is
+     also ``True``.
+
+     .. versionadded:: 0.9.0
+
+     .. seealso:: :meth:`.Join.alias`
+
     :param selectable: a table or select() statement that will
         be used in place of the generated FROM clause. This argument is
         required if any of the desired classes use concrete table
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index f0c6134e5..1ceaa9191 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -828,8 +828,14 @@ def alias(selectable, name=None, flat=False):
         If ``None``, a name will be deterministically generated
         at compile time.
 
+    :param flat: Will be passed through to if the given selectable
+     is an instance of :class:`.Join` - see :meth:`.Join.alias`
+     for details.
+
+     .. versionadded:: 0.9.0
+
     """
-    return Alias(selectable, name=name)
+    return selectable.alias(name=name, flat=flat)
 
 
 def literal(value, type_=None):
@@ -3983,14 +3989,23 @@ class Join(FromClause):
     def alias(self, name=None, flat=False):
         """return an alias of this :class:`.Join`.
 
-        Used against a :class:`.Join` object,
-        :meth:`~.Join.alias` calls the :meth:`~.Join.select`
-        method first so that a subquery against a
-        :func:`.select` construct is generated.
-        the :func:`~expression.select` construct also has the
-        ``correlate`` flag set to ``False`` and will not
-        auto-correlate inside an enclosing :func:`~expression.select`
-        construct.
+        The default behavior here is to first produce a SELECT
+        construct from this :class:`.Join`, then to produce a
+        :class:`.Alias` from that.  So given a join of the form::
+
+            j = table_a.join(table_b, table_a.c.id == table_b.c.a_id)
+
+        The JOIN by itself would look like::
+
+            table_a JOIN table_b ON table_a.id = table_b.a_id
+
+        Whereas the alias of the above, ``j.alias()``, would in a
+        SELECT context look like::
+
+            (SELECT table_a.id AS table_a_id, table_b.id AS table_b_id,
+                table_b.a_id AS table_b_a_id
+                FROM table_a
+                JOIN table_b ON table_a.id = table_b.a_id) AS anon_1
 
         The equivalent long-hand form, given a :class:`.Join` object
         ``j``, is::
@@ -4004,8 +4019,69 @@ class Join(FromClause):
                 name=name
             )
 
-        See :func:`~.expression.alias` for further details on
-        aliases.
+        The selectable produced by :meth:`.Join.alias` features the same
+        columns as that of the two individual selectables presented under
+        a single name - the individual columns are "auto-labeled", meaning
+        the ``.c.`` collection of the resulting :class:`.Alias` represents
+        the names of the individual columns using a ``<tablename>_<columname>``
+        scheme::
+
+            j.c.table_a_id
+            j.c.table_b_a_id
+
+        :meth:`.Join.alias` also features an alternate
+        option for aliasing joins which produces no enclosing SELECT and
+        does not normally apply labels to the column names.  The
+        ``flat=True`` option will call :meth:`.FromClause.alias`
+        against the left and right sides individually.
+        Using this option, no new ``SELECT`` is produced;
+        we instead, from a construct as below::
+
+            j = table_a.join(table_b, table_a.c.id == table_b.c.a_id)
+            j = j.alias(flat=True)
+
+        we get a result like this::
+
+            table_a AS table_a_1 JOIN table_b AS table_b_1 ON
+            table_a_1.id = table_b_1.a_id
+
+        The ``flat=True`` argument is also propagated to the contained
+        selectables, so that a composite join such as::
+
+            j = table_a.join(
+                    table_b.join(table_c,
+                            table_b.c.id == table_c.c.b_id),
+                    table_b.c.a_id == table_a.c.id
+                ).alias(flat=True)
+
+        Will produce an expression like::
+
+            table_a AS table_a_1 JOIN (
+                    table_b AS table_b_1 JOIN table_c AS table_c_1
+                    ON table_b_1.id = table_c_1.b_id
+            ) ON table_a_1.id = table_b_1.a_id
+
+        The standalone :func:`experssion.alias` function as well as the
+        base :meth:`.FromClause.alias` method also support the ``flat=True``
+        argument as a no-op, so that the argument can be passed to the
+        ``alias()`` method of any selectable.
+
+        .. versionadded:: 0.9.0 Added the ``flat=True`` option to create
+          "aliases" of joins without enclosing inside of a SELECT
+          subquery.
+
+        :param name: name given to the alias.
+
+        :param flat: if True, produce an alias of the left and right
+         sides of this :class:`.Join` and return the join of those
+         two selectables.   This produces join expression that does not
+         include an enclosing SELECT.
+
+         .. versionadded:: 0.9.0
+
+        .. seealso::
+
+            :func:`~.expression.alias`
 
         """
         if flat:
@@ -4236,6 +4312,9 @@ class FromGrouping(FromClause):
     def is_derived_from(self, element):
         return self.element.is_derived_from(element)
 
+    def alias(self, **kw):
+        return FromGrouping(self.element.alias(**kw))
+
     @property
     def _hide_froms(self):
         return self.element._hide_froms
diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py
index 16afcfc32..501cd3776 100644
--- a/test/sql/test_selectable.py
+++ b/test/sql/test_selectable.py
@@ -776,7 +776,80 @@ class AnonLabelTest(fixtures.TestBase):
         c1 = literal_column('x')
         eq_(str(select([c1.label('y')])), "SELECT x AS y")
 
-class JoinConditionTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
+class JoinAliasingTest(fixtures.TestBase, AssertsCompiledSQL):
+    __dialect__ = 'default'
+
+    def test_flat_ok_on_non_join(self):
+        a = table('a', column('a'))
+        s = a.select()
+        self.assert_compile(
+            s.alias(flat=True).select(),
+            "SELECT anon_1.a FROM (SELECT a.a AS a FROM a) AS anon_1"
+        )
+
+    def test_join_alias(self):
+        a = table('a', column('a'))
+        b = table('b', column('b'))
+        self.assert_compile(
+            a.join(b, a.c.a == b.c.b).alias(),
+            "SELECT a.a AS a_a, b.b AS b_b FROM a JOIN b ON a.a = b.b"
+        )
+
+    def test_join_standalone_alias(self):
+        a = table('a', column('a'))
+        b = table('b', column('b'))
+        self.assert_compile(
+            alias(a.join(b, a.c.a == b.c.b)),
+            "SELECT a.a AS a_a, b.b AS b_b FROM a JOIN b ON a.a = b.b"
+        )
+
+    def test_join_alias_flat(self):
+        a = table('a', column('a'))
+        b = table('b', column('b'))
+        self.assert_compile(
+            a.join(b, a.c.a == b.c.b).alias(flat=True),
+            "a AS a_1 JOIN b AS b_1 ON a_1.a = b_1.b"
+        )
+
+    def test_join_standalone_alias_flat(self):
+        a = table('a', column('a'))
+        b = table('b', column('b'))
+        self.assert_compile(
+            alias(a.join(b, a.c.a == b.c.b), flat=True),
+            "a AS a_1 JOIN b AS b_1 ON a_1.a = b_1.b"
+        )
+
+    def test_composed_join_alias_flat(self):
+        a = table('a', column('a'))
+        b = table('b', column('b'))
+        c = table('c', column('c'))
+        d = table('d', column('d'))
+
+        j1 = a.join(b, a.c.a == b.c.b)
+        j2 = c.join(d, c.c.c == d.c.d)
+        self.assert_compile(
+            j1.join(j2, b.c.b == c.c.c).alias(flat=True),
+            "a AS a_1 JOIN b AS b_1 ON a_1.a = b_1.b JOIN "
+            "(c AS c_1 JOIN d AS d_1 ON c_1.c = d_1.d) ON b_1.b = c_1.c"
+        )
+
+    def test_composed_join_alias(self):
+        a = table('a', column('a'))
+        b = table('b', column('b'))
+        c = table('c', column('c'))
+        d = table('d', column('d'))
+
+        j1 = a.join(b, a.c.a == b.c.b)
+        j2 = c.join(d, c.c.c == d.c.d)
+        self.assert_compile(
+            select([j1.join(j2, b.c.b == c.c.c).alias()]),
+            "SELECT anon_1.a_a, anon_1.b_b, anon_1.c_c, anon_1.d_d "
+            "FROM (SELECT a.a AS a_a, b.b AS b_b, c.c AS c_c, d.d AS d_d "
+            "FROM a JOIN b ON a.a = b.b "
+            "JOIN (c JOIN d ON c.c = d.d) ON b.b = c.c) AS anon_1"
+        )
+
+class JoinConditionTest(fixtures.TestBase, AssertsCompiledSQL):
     __dialect__ = 'default'
 
     def test_join_condition(self):