- epic rewrite of Query.join documentation

- remove cruft from tutorial regarding obsolete orm.join, old calling forms

2 files changed, 296 insertions, 99 deletions

diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index d9c67d56c..7f1d08f2f 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -1241,81 +1241,246 @@ class Query(object):
                 )
 
     def join(self, *props, **kwargs):
-        """Create a join against this ``Query`` object's criterion
-        and apply generatively, returning the newly resulting ``Query``.
-
-        Each element in \*props may be:
-
-          * a string property name, i.e. "rooms".  This will join along the
-            relationship of the same name from this Query's "primary" mapper,
-            if one is present.
-
-          * a class-mapped attribute, i.e. Houses.rooms.  This will create a
-            join from "Houses" table to that of the "rooms" relationship.
-
-        A two-element form of \*props may also be passed.   In this form,
-        the first element is a target class or selectable, the second
-        is a string property name, class-mapped attribute, or clause
-        construct representing an "ON" clause.   This supersedes the
-        previous "tuple" calling form - multiple join() calls should
-        be used for multiple (target, onclause) pairs.
+        """Create a SQL JOIN against this :class:`.Query` object's criterion
+        and apply generatively, returning the newly resulting :class:`.Query`.
+        
+        **Simple Relationship Joins**
+        
+        Consider a mapping between two classes ``User`` and ``Address``,
+        with a relationship ``User.addresses`` representing a collection 
+        of ``Address`` objects associated with each ``User``.   The most common 
+        usage of :meth:`~.Query.join` is to create a JOIN along this
+        relationship, using the ``User.addresses`` attribute as an indicator
+        for how this should occur::
+            
+            q = session.query(User).join(User.addresses)
+        
+        Where above, the call to :meth:`~.Query.join` along ``User.addresses`` 
+        will result in SQL equivalent to::
+        
+            SELECT user.* FROM user JOIN address ON user.id = address.user_id
+        
+        In the above example we refer to ``User.addresses`` as passed to
+        :meth:`~.Query.join` as the *on clause*, that is, it indicates
+        how the "ON" portion of the JOIN should be constructed.  For a 
+        single-entity query such as the one above (i.e. we start by selecting only from
+        ``User`` and nothing else), the relationship can also be specified by its 
+        string name::
+        
+            q = session.query(User).join("addresses")
+            
+        :meth:`~.Query.join` can also accommodate multiple 
+        "on clause" arguments to produce a chain of joins, such as below
+        where a join across four related entities is constructed::
+        
+            q = session.query(User).join("orders", "items", "keywords")
+            
+        The above would be shorthand for three separate calls to :meth:`~.Query.join`,
+        each using an explicit attribute to indicate the source entity::
+        
+            q = session.query(User).\\
+                    join(User.orders).\\
+                    join(Order.items).\\
+                    join(Item.keywords)
+        
+        **Joins to a Target Entity or Selectable**
+        
+        A second form of :meth:`~.Query.join` allows any mapped entity
+        or core selectable construct as a target.   In this usage, 
+        :meth:`~.Query.join` will attempt
+        to create a JOIN along the natural foreign key relationship between
+        two entities::
+        
+            q = session.query(User).join(Address)
+            
+        The above calling form of :meth:`.join` will raise an error if 
+        either there are no foreign keys between the two entities, or if 
+        there are multiple foreign key linkages between them.   In the
+        above calling form, :meth:`~.Query.join` is called upon to 
+        create the "on clause" automatically for us.  The target can
+        be any mapped entity or selectable, such as a :class:`.Table`::
+        
+            q = session.query(User).join(addresses_table)
+        
+        **Joins to a Target with an ON Clause**
+        
+        The third calling form allows both the target entity as well
+        as the ON clause to be passed explicitly.   Suppose for 
+        example we wanted to join to ``Address`` twice, using
+        an alias the second time.  We use :func:`~sqlalchemy.orm.aliased` 
+        to create a distinct alias of ``Address``, and join
+        to it using the ``target, onclause`` form, so that the 
+        alias can be specified explicitly as the target along with
+        the relationship to instruct how the ON clause should proceed::
+        
+            a_alias = aliased(Address)
+            
+            q = session.query(User).\\
+                    join(User.addresses).\\
+                    join(a_alias, User.addresses).\\
+                    filter(Address.email_address=='ed@foo.com').\\
+                    filter(a_alias.email_address=='ed@bar.com')
+        
+        Where above, the generated SQL would be similar to::
+        
+            SELECT user.* FROM user 
+                JOIN address ON user.id = address.user_id
+                JOIN address AS address_1 ON user.id=address_1.user_id
+                WHERE address.email_address = :email_address_1
+                AND address_1.email_address = :email_address_2
+        
+        The two-argument calling form of :meth:`~.Query.join` 
+        also allows us to construct arbitrary joins with SQL-oriented
+        "on clause" expressions, not relying upon configured relationships
+        at all.  Any SQL expression can be passed as the ON clause
+        when using the two-argument form, which should refer to the target
+        entity in some way as well as an applicable source entity::
+        
+            q = session.query(User).join(Address, User.id==Address.user_id)
+        
+        .. note:: In SQLAlchemy 0.6 and earlier, the two argument form of 
+           :meth:`~.Query.join` requires the usage of a tuple::
+           
+               query(User).join((Address, User.id==Address.user_id))
+           
+           This calling form is accepted in 0.7 and further, though
+           is not necessary unless multiple join conditions are passed to
+           a single :meth:`~.Query.join` call, which itself is also not
+           generally necessary as it is now equivalent to multiple
+           calls (this wasn't always the case).
+           
+        **Advanced Join Targeting and Adaption**
+
+        There is a lot of flexibility in what the "target" can be when using 
+        :meth:`~.Query.join`.   As noted previously, it also accepts 
+        :class:`.Table` constructs and other selectables such as :func:`.alias` 
+        and :func:`.select` constructs, with either the one or two-argument forms::
+
+            addresses_q = select([Address.user_id]).\\
+                            filter(Address.email_address.endswith("@bar.com")).\\
+                            alias()
 
-        e.g.::
+            q = session.query(User).\\
+                        join(addresses_q, addresses_q.c.user_id==User.id)
+        
+        :meth:`~.Query.join` also features the ability to *adapt* a 
+        :meth:`~sqlalchemy.orm.relationship` -driven ON clause to the target selectable.
+        Below we construct a JOIN from ``User`` to a subquery against ``Address``, allowing
+        the relationship denoted by ``User.addresses`` to *adapt* itself
+        to the altered target::
+        
+            address_subq = session.query(Address).\\
+                                filter(Address.email_address == 'ed@foo.com').\\
+                                subquery()
 
-            # join along string attribute names
-            session.query(Company).join('employees')
-            session.query(Company).join('employees', 'tasks')
-
-            # join the Person entity to an alias of itself,
-            # along the "friends" relationship
-            PAlias = aliased(Person)
-            session.query(Person).join(Palias, Person.friends)
-
-            # join from Houses to the "rooms" attribute on the
-            # "Colonials" subclass of Houses, then join to the
-            # "closets" relationship on Room
-            session.query(Houses).join(Colonials.rooms, Room.closets)
-
-            # join from Company entities to the "employees" collection,
-            # using "people JOIN engineers" as the target.  Then join
-            # to the "computers" collection on the Engineer entity.
-            session.query(Company).\\
-                        join(people.join(engineers), 'employees').\\
-                        join(Engineer.computers)
-
-            # join from Articles to Keywords, using the "keywords" attribute.
-            # assume this is a many-to-many relationship.
-            session.query(Article).join(Article.keywords)
-
-            # same thing, but spelled out entirely explicitly
-            # including the association table.
-            session.query(Article).join(article_keywords,
-                        Articles.id==article_keywords.c.article_id).\\
-                    join(Keyword, 
-                        Keyword.id==article_keywords.c.keyword_id)
-
-        \**kwargs include:
-
-            aliased - when joining, create anonymous aliases of each table.
-            This is used for self-referential joins or multiple joins to the
-            same table. Consider usage of the aliased(SomeClass) construct as
-            a more explicit approach to this.
-
-            from_joinpoint - the given join conditions will attempt 
-            to join from the right endpoint of the most recent join(),
-            instead of from the query's root entity.  I.e. any chain 
-            of joins, such as::
-
-                query.join(a, b, c) 
-
-            is equivalent to::
-
-                query.join(a).\\
-                        join(b, from_joinpoint=True).\\
-                        join(c, from_joinpoint=True)
+            q = session.query(User).join(address_subq, User.addresses)
+        
+        Producing SQL similar to::
+        
+            SELECT user.* FROM user 
+                JOIN (
+                    SELECT address.id AS id, 
+                            address.user_id AS user_id, 
+                            address.email_address AS email_address 
+                    FROM address 
+                    WHERE address.email_address = :email_address_1
+                ) AS anon_1 ON user.id = anon_1.user_id
+        
+        The above form allows one to fall back onto an explicit ON
+        clause at any time::
+        
+            q = session.query(User).\\
+                    join(address_subq, User.id==address_subq.c.user_id)
+        
+        **Controlling what to Join From**
+        
+        While :meth:`~.Query.join` exclusively deals with the "right"
+        side of the JOIN, we can also control the "left" side, in those
+        cases where it's needed, using :meth:`~.Query.select_from`.
+        Below we construct a query against ``Address`` but can still
+        make usage of ``User.addresses`` as our ON clause by instructing
+        the :class:`.Query` to select first from the ``User`` 
+        entity::
+        
+            q = session.query(Address).select_from(User).\\
+                            join(User.addresses).\\
+                            filter(User.name == 'ed')
+        
+        Which will produce SQL similar to::
+        
+            SELECT address.* FROM user 
+                JOIN address ON user.id=address.user_id 
+                WHERE user.name = :name_1
+        
+        **Constructing Aliases Anonymously**
         
-        See also :ref:`ormtutorial_joins` in the ORM tutorial.
+        :meth:`~.Query.join` can construct anonymous aliases
+        using the ``aliased=True`` flag.  This feature is useful
+        when a query is being joined algorithmically, such as
+        when querying self-referentially to an arbitrary depth::
         
+            q = session.query(Node).\\
+                    join("children", "children", aliased=True)
+        
+        When ``aliased=True`` is used, the actual "alias" construct
+        is not explicitly available.  To work with it, methods such as 
+        :meth:`.Query.filter` will adapt the incoming entity to 
+        the last join point::
+        
+            q = session.query(Node).\\
+                    join("children", "children", aliased=True).\\
+                    filter(Node.name == 'grandchild 1')
+        
+        When using automatic aliasing, the ``from_joinpoint=True``
+        argument can allow a multi-node join to be broken into
+        multiple calls to :meth:`~.Query.join`, so that
+        each path along the way can be further filtered::
+        
+            q = session.query(Node).\\
+                    join("children", aliased=True).\\
+                    filter(Node.name='child 1').\\
+                    join("children", aliased=True, from_joinpoint=True).\\
+                    filter(Node.name == 'grandchild 1')
+        
+        The filtering aliases above can then be reset back to the
+        original ``Node`` entity using :meth:`~.Query.reset_joinpoint`::
+        
+            q = session.query(Node).\\
+                    join("children", "children", aliased=True).\\
+                    filter(Node.name == 'grandchild 1').\\
+                    reset_joinpoint().\\
+                    filter(Node.name == 'parent 1)
+        
+        For an example of ``aliased=True``, see the distribution 
+        example :ref:`examples_xmlpersistence` which illustrates
+        an XPath-like query system using algorithmic joins.
+        
+        :param *props: A collection of one or more join conditions, 
+         each consisting of a relationship-bound attribute or string 
+         relationship name representing an "on clause", or a single 
+         target entity, or a tuple in the form of ``(target, onclause)``.
+         A special two-argument calling form of the form ``target, onclause``
+         is also accepted.
+        :param aliased=False: If True, indicate that the JOIN target should be 
+         anonymously aliased.  Subsequent calls to :class:`~.Query.filter`
+         and similar will adapt the incoming criterion to the target 
+         alias, until :meth:`~.Query.reset_joinpoint` is called.
+        :param from_joinpoint=False: When using ``aliased=True``, a setting
+         of True here will cause the join to be from the most recent
+         joined target, rather than starting back from the original 
+         FROM clauses of the query.
+         
+        See also:
+        
+            :ref:`ormtutorial_joins` in the ORM tutorial.
+
+            :ref:`inheritance_toplevel` for details on how :meth:`~.Query.join`
+            is used for inheritance relationships.
+        
+            :func:`.orm.join` - a standalone ORM-level join function,
+            used internally by :meth:`.Query.join`, which in previous 
+            SQLAlchemy versions was the primary ORM-level joining interface.
+             
         """
         aliased, from_joinpoint = kwargs.pop('aliased', False),\
                                     kwargs.pop('from_joinpoint', False)
@@ -1607,12 +1772,13 @@ class Query(object):
 
     @_generative(_no_statement_condition)
     def reset_joinpoint(self):
-        """return a new Query reset the 'joinpoint' of this Query reset
-        back to the starting mapper.  Subsequent generative calls will
-        be constructed from the new joinpoint.
-
-        Note that each call to join() or outerjoin() also starts from
-        the root.
+        """Return a new :class:`.Query`, where the "join point" has
+        been reset back to the base FROM entities of the query.
+        
+        This method is usually used in conjunction with the
+        ``aliased=True`` feature of the :meth:`~.Query.join`
+        method.  See the example in :meth:`~.Query.join` for how 
+        this is used.
 
         """
         self._reset_joinpoint()
@@ -1622,13 +1788,16 @@ class Query(object):
         """Set the FROM clause of this :class:`.Query` explicitly.
 
         Sending a mapped class or entity here effectively replaces the
-        "left edge" of any calls to :meth:`.Query.join`, when no 
+        "left edge" of any calls to :meth:`~.Query.join`, when no 
         joinpoint is otherwise established - usually, the default "join
-        point" is the leftmost entity in the :class:`.Query` object's
+        point" is the leftmost entity in the :class:`~.Query` object's
         list of entities to be selected.
 
-        Mapped entities or plain :class:`.Table` or other selectables
+        Mapped entities or plain :class:`~.Table` or other selectables
         can be sent here which will form the default FROM clause.
+        
+        See the example in :meth:`~.Query.join` for a typical 
+        usage of :meth:`~.Query.select_from`.
 
         """
         obj = []
@@ -1983,20 +2152,25 @@ class Query(object):
                 break
 
     def merge_result(self, iterator, load=True):
-        """Merge a result into this Query's Session.
+        """Merge a result into this :class:`.Query` object's Session.
 
-        Given an iterator returned by a Query of the same structure as this
+        Given an iterator returned by a :class:`.Query` of the same structure as this
         one, return an identical iterator of results, with all mapped
-        instances merged into the session using Session.merge(). This is an
+        instances merged into the session using :meth:`.Session.merge`. This is an
         optimized method which will merge all mapped instances, preserving the
         structure of the result rows and unmapped columns with less method
-        overhead than that of calling Session.merge() explicitly for each
+        overhead than that of calling :meth:`.Session.merge` explicitly for each
         value.
 
         The structure of the results is determined based on the column list of
-        this Query - if these do not correspond, unchecked errors will occur.
+        this :class:`.Query` - if these do not correspond, unchecked errors will occur.
 
-        The 'load' argument is the same as that of Session.merge().
+        The 'load' argument is the same as that of :meth:`.Session.merge`.
+        
+        For an example of how :meth:`~.Query.merge_result` is used, see
+        the source code for the example :ref:`examples_caching`, where
+        :meth:`~.Query.merge_result` is used to efficiently restore state
+        from a cache back into a target :class:`.Session`.
 
         """
 
diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py
index 8448b545c..d57b04f0c 100644
--- a/lib/sqlalchemy/orm/util.py
+++ b/lib/sqlalchemy/orm/util.py
@@ -381,29 +381,52 @@ class _ORMJoin(expression.Join):
 
 def join(left, right, onclause=None, isouter=False, join_to_left=True):
     """Produce an inner join between left and right clauses.
-
-    In addition to the interface provided by
-    :func:`~sqlalchemy.sql.expression.join()`, left and right may be mapped
-    classes or AliasedClass instances. The onclause may be a
-    string name of a relationship(), or a class-bound descriptor
-    representing a relationship.
-
-    join_to_left indicates to attempt aliasing the ON clause,
+    
+    :func:`.orm.join` is an extension to the core join interface
+    provided by :func:`.sql.expression.join()`, where the
+    left and right selectables may be not only core selectable
+    objects such as :class:`.Table`, but also mapped classes or
+    :class:`.AliasedClass` instances.   The "on" clause can
+    be a SQL expression, or an attribute or string name
+    referencing a configured :func:`.relationship`.
+
+    ``join_to_left`` indicates to attempt aliasing the ON clause,
     in whatever form it is passed, to the selectable
     passed as the left side.  If False, the onclause
     is used as is.
+    
+    :func:`.orm.join` is not commonly needed in modern usage,
+    as its functionality is encapsulated within that of the
+    :meth:`.Query.join` method, which features a
+    significant amount of automation beyond :func:`.orm.join`
+    by itself.  Explicit usage of :func:`.orm.join` 
+    with :class:`.Query` involves usage of the 
+    :meth:`.Query.select_from` method, as in::
+    
+        from sqlalchemy.orm import join
+        session.query(User).\\
+            select_from(join(User, Address, User.addresses)).\\
+            filter(Address.email_address=='foo@bar.com')
+    
+    In modern SQLAlchemy the above join can be written more 
+    succinctly as::
+    
+        session.query(User).\\
+                join(User.addresses).\\
+                filter(Address.email_address=='foo@bar.com')
 
+    See :meth:`.Query.join` for information on modern usage
+    of ORM level joins.
+    
     """
     return _ORMJoin(left, right, onclause, isouter, join_to_left)
 
 def outerjoin(left, right, onclause=None, join_to_left=True):
     """Produce a left outer join between left and right clauses.
 
-    In addition to the interface provided by
-    :func:`~sqlalchemy.sql.expression.outerjoin()`, left and right may be
-    mapped classes or AliasedClass instances. The onclause may be a string
-    name of a relationship(), or a class-bound descriptor representing a
-    relationship.
+    This is the "outer join" version of the :func:`.orm.join` function,
+    featuring the same behavior except that an OUTER JOIN is generated.
+    See that function's documentation for other usage details.
 
     """
     return _ORMJoin(left, right, onclause, True, join_to_left)