Commits

Mike Bayer  committed 30bf130

- Query.from_self() as well as query.subquery() both disable
the rendering of eager joins inside the subquery produced.
The "disable all eager joins" feature is available publically
via a new query.enable_eagerloads() generative. [ticket:1276]
- Added a rudimental series of set operations to Query that
receive Query objects as arguments, including union(),
union_all(), intersect(), except_(), insertsect_all(),
except_all(). See the API documentation for
Query.union() for examples.
- Fixed bug that prevented Query.join() and eagerloads from
attaching to a query that selected from a union or aliased union.

  • Participants
  • Parent commits 4504e32

Comments (0)

Files changed (7)

 
     - Test coverage added for `relation()` objects specified on
       concrete mappers. [ticket:1237]
-
+    
+    - Query.from_self() as well as query.subquery() both disable 
+      the rendering of eager joins inside the subquery produced.
+      The "disable all eager joins" feature is available publically
+      via a new query.enable_eagerloads() generative. [ticket:1276]
+      
+    - Added a rudimental series of set operations to Query that
+      receive Query objects as arguments, including union(),
+      union_all(), intersect(), except_(), insertsect_all(),
+      except_all().  See the API documentation for
+      Query.union() for examples. 
+
+    - Fixed bug that prevented Query.join() and eagerloads from 
+      attaching to a query that selected from a union or aliased union.
+      
     - A short documentation example added for bidirectional
       relations specified on concrete mappers. [ticket:1237]
 

File lib/sqlalchemy/orm/query.py

         self._correlate = set()
         self._joinpoint = None
         self._with_labels = False
+        self._enable_eagerloads = True
         self.__joinable_tables = None
         self._having = None
         self._populate_existing = False
 
     def __set_select_from(self, from_obj):
         if isinstance(from_obj, expression._SelectBaseMixin):
-            # alias SELECTs and unions
             from_obj = from_obj.alias()
 
         self._from_obj = from_obj
         equivs = self.__all_equivs()
 
         if isinstance(from_obj, expression.Alias):
-            # dont alias a regular join (since its not an alias itself)
             self._from_obj_alias = sql_util.ColumnAdapter(self._from_obj, equivs)
-
+            
     def _get_polymorphic_adapter(self, entity, selectable):
         self.__mapper_loads_polymorphically_with(entity.mapper, sql_util.ColumnAdapter(selectable, entity.mapper._equivalent_columns))
 
     @property
     def statement(self):
         """The full SELECT statement represented by this Query."""
+        
         return self._compile_context(labels=self._with_labels).statement._annotate({'_halt_adapt': True})
 
+    @property
+    def _nested_statement(self):
+        return self.with_labels().enable_eagerloads(False).statement.correlate(None)
+
     def subquery(self):
-        """return the full SELECT statement represented by this Query, embedded within an Alias."""
+        """return the full SELECT statement represented by this Query, embedded within an Alias.
+        
+        Eager JOIN generation within the query is disabled.
+        
+        """
 
-        return self.statement.alias()
+        return self.enable_eagerloads(False).statement.alias()
 
     @_generative()
+    def enable_eagerloads(self, value):
+        """Control whether or not eager joins are rendered.
+        
+        When set to False, the returned Query will not render 
+        eager joins regardless of eagerload() options
+        or mapper-level lazy=False configurations.
+        
+        This is used primarily when nesting the Query's
+        statement into a subquery or other
+        selectable.
+        
+        """
+        self._enable_eagerloads = value
+        
+    @_generative()
     def with_labels(self):
         """Apply column labels to the return value of Query.statement.
 
         m = _MapperEntity(self, entity)
         self.__setup_aliasizers([m])
 
-    @_generative()
     def from_self(self, *entities):
         """return a Query that selects from this Query's SELECT statement.
 
         \*entities - optional list of entities which will replace
         those being selected.
+
         """
+        fromclause = self._nested_statement
+        q = self._from_selectable(fromclause)
+        if entities:
+            q._set_entities(entities)
+        return q
 
-        fromclause = self.with_labels().statement.correlate(None)
+    _from_self = from_self
+
+    @_generative()
+    def _from_selectable(self, fromclause):
         self._statement = self._criterion = None
         self._order_by = self._group_by = self._distinct = False
         self._limit = self._offset = None
         self.__set_select_from(fromclause)
-        if entities:
-            self._set_entities(entities)
-
-    _from_self = from_self
 
     def values(self, *columns):
         """Return an iterator yielding result tuples corresponding to the given list of columns"""
         else:
             self._having = criterion
 
+    def union(self, *q):
+        """Produce a UNION of this Query against one or more queries.
+
+        e.g.::
+
+            q1 = sess.query(SomeClass).filter(SomeClass.foo=='bar')
+            q2 = sess.query(SomeClass).filter(SomeClass.bar=='foo')
+
+            q3 = q1.union(q2)
+            
+        The method accepts multiple Query objects so as to control
+        the level of nesting.  A series of ``union()`` calls such as::
+        
+            x.union(y).union(z).all()
+            
+        will nest on each ``union()``, and produces::
+        
+            SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y) UNION SELECT * FROM Z)
+            
+        Whereas::
+        
+            x.union(y, z).all()
+            
+        produces::
+
+            SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION SELECT * FROM Z)
+
+        """
+        return self._from_selectable(
+                    expression.union(*([self._nested_statement]+ [x._nested_statement for x in q])))
+
+    def union_all(self, *q):
+        """Produce a UNION ALL of this Query against one or more queries.
+
+        Works the same way as :method:`union`.  See that
+        method for usage examples.
+
+        """
+        return self._from_selectable(
+                    expression.union_all(*([self._nested_statement]+ [x._nested_statement for x in q]))
+                )
+
+    def intersect(self, *q):
+        """Produce an INTERSECT of this Query against one or more queries.
+
+        Works the same way as :method:`union`.  See that
+        method for usage examples.
+
+        """
+        return self._from_selectable(
+                    expression.intersect(*([self._nested_statement]+ [x._nested_statement for x in q]))
+                )
+
+    def intersect_all(self, *q):
+        """Produce an INTERSECT ALL of this Query against one or more queries.
+
+        Works the same way as :method:`union`.  See that
+        method for usage examples.
+
+        """
+        return self._from_selectable(
+                    expression.intersect_all(*([self._nested_statement]+ [x._nested_statement for x in q]))
+                )
+
+    def except_(self, *q):
+        """Produce an EXCEPT of this Query against one or more queries.
+
+        Works the same way as :method:`union`.  See that
+        method for usage examples.
+
+        """
+        return self._from_selectable(
+                    expression.except_(*([self._nested_statement]+ [x._nested_statement for x in q]))
+                )
+
+    def except_all(self, *q):
+        """Produce an EXCEPT ALL of this Query against one or more queries.
+
+        Works the same way as :method:`union`.  See that
+        method for usage examples.
+
+        """
+        return self._from_selectable(
+                    expression.except_all(*([self._nested_statement]+ [x._nested_statement for x in q]))
+                )
+
     @util.accepts_a_list_as_starargs(list_deprecation='pending')
     def join(self, *props, **kwargs):
         """Create a join against this ``Query`` object's criterion
         self.primary_columns = []
         self.secondary_columns = []
         self.eager_order_by = []
-
+        self.enable_eagerloads = query._enable_eagerloads
         self.eager_joins = {}
         self.froms = []
         self.adapter = None

File lib/sqlalchemy/orm/strategies.py

     def setup_query(self, context, entity, path, adapter, column_collection=None, parentmapper=None, **kwargs):
         """Add a left outer join to the statement thats being constructed."""
 
+        if not context.enable_eagerloads:
+            return
+            
         path = path + (self.key,)
 
         # check for user-defined eager alias
         # whether or not the Query will wrap the selectable in a subquery,
         # and then attach eager load joins to that (i.e., in the case of LIMIT/OFFSET etc.)
         should_nest_selectable = context.query._should_nest_selectable
-    
+        
         if entity in context.eager_joins:
             entity_key, default_towrap = entity, entity.selectable
         elif should_nest_selectable or not context.from_clause or not sql_util.search(context.from_clause, entity.selectable):

File lib/sqlalchemy/orm/util.py

             if isinstance(onclause, basestring):
                 prop = left_mapper.get_property(onclause)
             elif isinstance(onclause, attributes.QueryableAttribute):
+                # TODO: we might want to honor the current adapt_from,
+                # if already set.  we would need to adjust how we calculate
+                # adapt_from though since it is present in too many cases
+                # at the moment (query tests illustrate that).
                 adapt_from = onclause.__clause_element__()
                 prop = onclause.property
             elif isinstance(onclause, MapperProperty):

File lib/sqlalchemy/sql/expression.py

     def self_group(self, against=None):
         return _FromGrouping(self)
 
+    def is_derived_from(self, fromclause):
+        for s in self.selects:
+            if s.is_derived_from(fromclause):
+                return True
+        return False
+
     def _populate_column_collection(self):
         for cols in zip(*[s.c for s in self.selects]):
             proxy = cols[0]._make_proxy(self, name=self.use_labels and cols[0]._label or None)

File test/orm/query.py

         self.assertEquals([User(id=7),User(id=8),User(id=9)], sess.query(User).filter(User.addresses!=None).order_by(User.id).all())
 
 
-class FromSelfTest(QueryTest):
+class FromSelfTest(QueryTest, AssertsCompiledSQL):
     def test_filter(self):
 
         assert [User(id=8), User(id=9)] == create_session().query(User).filter(User.id.in_([8,9]))._from_self().all()
             (User(id=8), Address(id=3)),
             (User(id=8), Address(id=4)),
             (User(id=9), Address(id=5))
-        ] == create_session().query(User).filter(User.id.in_([8,9]))._from_self().join('addresses').add_entity(Address).order_by(User.id, Address.id).all()
+        ] == create_session().query(User).filter(User.id.in_([8,9]))._from_self().\
+            join('addresses').add_entity(Address).order_by(User.id, Address.id).all()
     
+    def test_no_eagerload(self):
+        """test that eagerloads are pushed outwards and not rendered in subqueries."""
+        
+        s = create_session()
+        
+        self.assert_compile(
+            s.query(User).options(eagerload(User.addresses)).from_self().statement,
+            "SELECT anon_1.users_id, anon_1.users_name, addresses_1.id, addresses_1.user_id, "\
+            "addresses_1.email_address FROM (SELECT users.id AS users_id, users.name AS users_name FROM users) AS anon_1 "\
+            "LEFT OUTER JOIN addresses AS addresses_1 ON anon_1.users_id = addresses_1.user_id ORDER BY addresses_1.id"
+        )
+            
+        
     def test_multiple_entities(self):
         sess = create_session()
 
             #    order_by(User.id, Address.id).first(),
             (User(id=8, addresses=[Address(), Address(), Address()]), Address(id=2)),
         )
+    
+class SetOpsTest(QueryTest, AssertsCompiledSQL):
+    
+    def test_union(self):
+        s = create_session()
+        
+        fred = s.query(User).filter(User.name=='fred')
+        ed = s.query(User).filter(User.name=='ed')
+        jack = s.query(User).filter(User.name=='jack')
+        
+        self.assertEquals(fred.union(ed).order_by(User.name).all(), 
+            [User(name='ed'), User(name='fred')]
+        )
+
+        self.assertEquals(fred.union(ed, jack).order_by(User.name).all(), 
+            [User(name='ed'), User(name='fred'), User(name='jack')]
+        )
+        
+    @testing.fails_on('mysql', "mysql doesn't support intersect")
+    def test_intersect(self):
+        s = create_session()
+
+        fred = s.query(User).filter(User.name=='fred')
+        ed = s.query(User).filter(User.name=='ed')
+        jack = s.query(User).filter(User.name=='jack')
+        self.assertEquals(fred.intersect(ed, jack).all(), 
+            []
+        )
+
+        self.assertEquals(fred.union(ed).intersect(ed.union(jack)).all(), 
+            [User(name='ed')]
+        )
+    
+    def test_eager_load(self):
+        s = create_session()
+
+        fred = s.query(User).filter(User.name=='fred')
+        ed = s.query(User).filter(User.name=='ed')
+        jack = s.query(User).filter(User.name=='jack')
+
+        def go():
+            self.assertEquals(
+                fred.union(ed).order_by(User.name).options(eagerload(User.addresses)).all(), 
+                [
+                    User(name='ed', addresses=[Address(), Address(), Address()]), 
+                    User(name='fred', addresses=[Address()])
+                ]
+            )
+        self.assert_sql_count(testing.db, go, 1)
+        
         
 class AggregateTest(QueryTest):
 

File test/sql/generative.py

         assert str(u) == str(u2) == str(u3)
         assert u2.compile().params == {'id_param':7}
         assert u3.compile().params == {'id_param':10}
+    
+    def test_adapt_union(self):
+        u = union(t1.select().where(t1.c.col1==4), t1.select().where(t1.c.col1==5)).alias()
+        
+        assert sql_util.ClauseAdapter(u).traverse(t1) is u
         
     def test_binds(self):
         """test that unique bindparams change their name upon clone() to prevent conflicts"""