From 41802e102cf1436930f43d1ec19005bcceb41691 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Sat, 24 Feb 2007 00:17:08 +0000
Subject: [PATCH] - correlated subqueries work inside of ORDER BY, GROUP BY

---
 CHANGES               |  1 +
 lib/sqlalchemy/sql.py | 17 +++++++++++++----
 test/sql/select.py    | 25 +++++++++++++++++++------
 3 files changed, 33 insertions(+), 10 deletions(-)

diff --git a/CHANGES b/CHANGES
index 8602e82db3..2024e3a63e 100644
--- a/CHANGES
+++ b/CHANGES
@@ -1,6 +1,7 @@
 - sql:
     - exists() becomes useable as a standalone selectable, not just in a 
     WHERE clause
+    - correlated subqueries work inside of ORDER BY, GROUP BY
 - orm:
     - a full select() construct can be passed to query.select() (which
       worked anyway), but also query.selectfirst(), query.selectone() which
diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py
index 3acd76258a..ffb4dc7510 100644
--- a/lib/sqlalchemy/sql.py
+++ b/lib/sqlalchemy/sql.py
@@ -918,6 +918,8 @@ class ClauseList(ClauseElement):
             if c is None: continue
             self.append(c)
         self.parens = kwargs.get('parens', False)
+    def __iter__(self):
+        return iter(self.clauses)
     def copy_container(self):
         clauses = [clause.copy_container() for clause in self.clauses]
         return ClauseList(parens=self.parens, *clauses)
@@ -1494,16 +1496,22 @@ class Select(_SelectBaseMixin, FromClause):
         self.__correlator = Select._CorrelatedVisitor(self, False)
         self.__wherecorrelator = Select._CorrelatedVisitor(self, True)
 
-        self.group_by(*(group_by or [None]))
-        self.order_by(*(order_by or [None]))
-        
+
         if columns is not None:
             for c in columns:
                 self.append_column(c)
 
+        self.order_by(*(order_by or [None]))
+        self.group_by(*(group_by or [None]))
+        for c in self.order_by_clause:
+            c.accept_visitor(self.__correlator)
+        for c in self.group_by_clause:
+            c.accept_visitor(self.__correlator)
+
         for f in from_obj:
             self.append_from(f)
 
+
         # whereclauses must be appended after the columns/FROM, since it affects
         # the correlation of subqueries.  see test/sql/select.py SelectTest.testwheresubquery
         if whereclause is not None:
@@ -1549,7 +1557,8 @@ class Select(_SelectBaseMixin, FromClause):
         
         # visit the FROM objects of the column looking for more Selects
         for f in column._get_from_objects():
-            f.accept_visitor(self.__correlator)
+            if f is not self:
+                f.accept_visitor(self.__correlator)
         self._process_froms(column, False)
     def _make_proxy(self, selectable, name):
         if self.is_scalar:
diff --git a/test/sql/select.py b/test/sql/select.py
index 7473d4f638..14a5009f10 100644
--- a/test/sql/select.py
+++ b/test/sql/select.py
@@ -162,6 +162,17 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
             s,
             "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE EXISTS (SELECT 1 FROM myothertable WHERE myothertable.otherid = mytable.myid)"
         )
+    
+    def testorderbysubquery(self):
+        self.runtest(
+            table1.select(order_by=[select([table2.c.otherid], table1.c.myid==table2.c.otherid)]),
+            "SELECT mytable.myid, mytable.name, mytable.description FROM mytable ORDER BY (SELECT myothertable.otherid AS otherid FROM myothertable WHERE mytable.myid = myothertable.otherid)"
+        )
+        self.runtest(
+            table1.select(order_by=[desc(select([table2.c.otherid], table1.c.myid==table2.c.otherid))]),
+            "SELECT mytable.myid, mytable.name, mytable.description FROM mytable ORDER BY (SELECT myothertable.otherid AS otherid FROM myothertable WHERE mytable.myid = myothertable.otherid) DESC"
+        )
+        
         
     def testcolumnsubquery(self):
         s = select([table1.c.myid], scalar=True, correlate=False)
@@ -566,15 +577,17 @@ myothertable.othername != :myothertable_othername AND EXISTS (select yay from fo
         self.runtest(query.select(), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable, myothertable, thirdtable WHERE mytable.myid = myothertable.otherid(+) AND thirdtable.userid(+) = myothertable.otherid", dialect=oracle.dialect(use_ansi=False))    
 
     def testbindparam(self):
-        self.runtest(select(
+        for stmt, assertion in [
+            (
+                select(
                     [table1, table2],
                     and_(table1.c.myid == table2.c.otherid,
-                    table1.c.name == bindparam('mytablename'),
-                    )
-                ),
+                    table1.c.name == bindparam('mytablename')),
                 "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername \
-FROM mytable, myothertable WHERE mytable.myid = myothertable.otherid AND mytable.name = :mytablename"
-                )
+        FROM mytable, myothertable WHERE mytable.myid = myothertable.otherid AND mytable.name = :mytablename"
+            )
+        ]:
+            self.runtest(stmt, assertion)
 
         # check that the bind params sent along with a compile() call
         # get preserved when the params are retreived later
-- 
2.47.2