From bda6f1e06ff3a7d63675833d9bb0d52e82852836 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Fri, 29 Feb 2008 21:54:40 +0000
Subject: [PATCH] - setting the relation()-level order by to a column in the
 many-to-many "secondary" table will now work with eager loading, previously
 the "order by" wasn't aliased against the secondary table's alias.

---
 CHANGES                     |  5 ++++
 lib/sqlalchemy/orm/util.py  | 16 +++++++----
 test/orm/eager_relations.py | 55 ++++++++++++++++++++++++++++++++++++-
 3 files changed, 70 insertions(+), 6 deletions(-)

diff --git a/CHANGES b/CHANGES
index c2bd34e8aa..501db5d286 100644
--- a/CHANGES
+++ b/CHANGES
@@ -59,6 +59,11 @@ CHANGES
       populating the PK value of <somechild> in the case that
       <somechild> was pending.
 
+    - setting the relation()-level order by to a column in the
+      many-to-many "secondary" table will now work with eager 
+      loading, previously the "order by" wasn't aliased against
+      the secondary table's alias.
+      
 - dialects
     - Invalid SQLite connection URLs now raise an error.
 
diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py
index 9ddfcd278f..8a3583c36f 100644
--- a/lib/sqlalchemy/orm/util.py
+++ b/lib/sqlalchemy/orm/util.py
@@ -214,17 +214,23 @@ class PropertyAliasedClauses(AliasedClauses):
             self.primaryjoin = primary_aliasizer.traverse(primaryjoin, clone=True)
         else:
             if parentclauses is not None: 
-                aliasizer = sql_util.ClauseAdapter(self.alias, exclude=prop.local_side)
-                aliasizer.chain(sql_util.ClauseAdapter(parentclauses.alias, exclude=prop.remote_side))
+                primary_aliasizer = sql_util.ClauseAdapter(self.alias, exclude=prop.local_side)
+                primary_aliasizer.chain(sql_util.ClauseAdapter(parentclauses.alias, exclude=prop.remote_side))
             else:
-                aliasizer = sql_util.ClauseAdapter(self.alias, exclude=prop.local_side)
+                primary_aliasizer = sql_util.ClauseAdapter(self.alias, exclude=prop.local_side)
 
-            self.primaryjoin = aliasizer.traverse(primaryjoin, clone=True)
+            self.primaryjoin = primary_aliasizer.traverse(primaryjoin, clone=True)
             self.secondary = None
             self.secondaryjoin = None
         
         if prop.order_by:
-            self.order_by = sql_util.ClauseAdapter(self.alias).copy_and_process(util.to_list(prop.order_by))
+            if prop.secondary:
+                # usually this is not used but occasionally someone has a sort key in their secondary
+                # table, even tho SA does not support writing this column directly
+                self.order_by = secondary_aliasizer.copy_and_process(util.to_list(prop.order_by))
+            else:
+                self.order_by = primary_aliasizer.copy_and_process(util.to_list(prop.order_by))
+                
         else:
             self.order_by = None
     
diff --git a/test/orm/eager_relations.py b/test/orm/eager_relations.py
index f35fbcbfc2..5553b9c797 100644
--- a/test/orm/eager_relations.py
+++ b/test/orm/eager_relations.py
@@ -74,7 +74,7 @@ class EagerTest(FixtureTest):
             User(id=10, addresses=[])
         ] == q.all()
 
-    def test_orderby_secondary(self):
+    def test_orderby_related(self):
         """tests that a regular mapper select on a single table can order by a relation to a second table"""
 
         mapper(Address, addresses)
@@ -649,6 +649,59 @@ class AddEntityTest(FixtureTest):
             self.assertEquals(ret, self._assert_result())
         self.assert_sql_count(testing.db, go, 1)
 
+class OrderBySecondaryTest(ORMTest):
+    def define_tables(self, metadata):
+        global a, b, m2m
+        m2m = Table('mtom', metadata, 
+            Column('id', Integer, primary_key=True),
+            Column('aid', Integer, ForeignKey('a.id')),
+            Column('bid', Integer, ForeignKey('b.id')),
+            )
+            
+        a = Table('a', metadata,
+            Column('id', Integer, primary_key=True),
+            Column('data', String(50)),
+            )
+        b = Table('b', metadata,
+            Column('id', Integer, primary_key=True),
+            Column('data', String(50)),
+            )
+
+    def insert_data(self):
+        a.insert().execute([
+            {'id':1, 'data':'a1'},
+            {'id':2, 'data':'a2'}
+        ])
+        
+        b.insert().execute([
+            {'id':1, 'data':'b1'},
+            {'id':2, 'data':'b2'},
+            {'id':3, 'data':'b3'},
+            {'id':4, 'data':'b4'},
+        ])
+        
+        m2m.insert().execute([
+            {'id':2, 'aid':1, 'bid':1},
+            {'id':4, 'aid':2, 'bid':4},
+            {'id':1, 'aid':1, 'bid':3},
+            {'id':6, 'aid':2, 'bid':2},
+            {'id':3, 'aid':1, 'bid':2},
+            {'id':5, 'aid':2, 'bid':3},
+        ])
+    
+    def test_ordering(self):
+        class A(Base):pass
+        class B(Base):pass
+        
+        mapper(A, a, properties={
+            'bs':relation(B, secondary=m2m, lazy=False, order_by=m2m.c.id)
+        })
+        mapper(B, b)
+        
+        sess = create_session()
+        self.assertEquals(sess.query(A).all(), [A(data='a1', bs=[B(data='b3'), B(data='b1'), B(data='b2')]), A(bs=[B(data='b4'), B(data='b3'), B(data='b2')])])
+        
+        
 class SelfReferentialEagerTest(ORMTest):
     def define_tables(self, metadata):
         global nodes
-- 
2.47.3