From 3984cad7228fcc8fae9c3be93ecd86f6b08ed4b3 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 15 Oct 2009 20:15:19 +0000 Subject: [PATCH] - query.from_self(), query.union(), others which do a "SELECT * from (SELECT...)" type of nesting will do a better job translating column expressions within the subquery to the columns clause of the outer query. This is potentially backwards incompatible with 0.5, in that this may break queries with literal expressions that do not have labels applied (i.e. literal('foo'), etc.) [ticket:1568] --- CHANGES | 9 ++++++ lib/sqlalchemy/orm/query.py | 14 ++++++++- test/orm/test_query.py | 63 ++++++++++++++++++++++++++++++++----- 3 files changed, 77 insertions(+), 9 deletions(-) diff --git a/CHANGES b/CHANGES index e67373262f..9fa1632a23 100644 --- a/CHANGES +++ b/CHANGES @@ -43,6 +43,15 @@ CHANGES multiple comma separated entries within the FROM clause. Useful when selecting from multiple-homed join() clauses. + - query.from_self(), query.union(), others which do a + "SELECT * from (SELECT...)" type of nesting will do + a better job translating column expressions within the subquery + to the columns clause of the outer query. This is + potentially backwards incompatible with 0.5, in that this + may break queries with literal expressions that do not have labels + applied (i.e. literal('foo'), etc.) + [ticket:1568] + - the "dont_load=True" flag on Session.merge() is deprecated and is now "load=False". diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py index b347e205e8..d5f21c7e0e 100644 --- a/lib/sqlalchemy/orm/query.py +++ b/lib/sqlalchemy/orm/query.py @@ -634,6 +634,10 @@ class Query(object): self._order_by = self._group_by = self._distinct = False self._limit = self._offset = None self._set_select_from(fromclause) + old_entities = self._entities + self._entities = [] + for e in old_entities: + e.adapt_to_selectable(self, self._from_obj[0]) def values(self, *columns): """Return an iterator yielding result tuples corresponding to the given list of columns""" @@ -819,6 +823,8 @@ class Query(object): SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION SELECT * FROM Z) """ + + return self._from_selectable( expression.union(*([self]+ list(q)))) @@ -1968,6 +1974,9 @@ class _MapperEntity(_QueryEntity): else: return entity.base_mapper is self.path_entity + def adapt_to_selectable(self, query, sel): + query._entities.append(self) + def _get_entity_clauses(self, query, context): adapter = None @@ -2117,7 +2126,10 @@ class _ColumnEntity(_QueryEntity): self.entity_zero = list(self.entities)[0] else: self.entity_zero = None - + + def adapt_to_selectable(self, query, sel): + _ColumnEntity(query, sel.corresponding_column(self.column)) + def setup_entity(self, entity, mapper, adapter, from_obj, is_aliased_class, with_polymorphic): self.selectable = from_obj self.froms.add(from_obj) diff --git a/test/orm/test_query.py b/test/orm/test_query.py index a9319608dc..fdbd32abc6 100644 --- a/test/orm/test_query.py +++ b/test/orm/test_query.py @@ -920,6 +920,40 @@ class SetOpsTest(QueryTest, AssertsCompiledSQL): eq_(fred.union(ed, jack).order_by(User.name).all(), [User(name='ed'), User(name='fred'), User(name='jack')] ) + + def test_union_labels(self): + """test that column expressions translate during the _from_statement() portion of union(), others""" + + s = create_session() + q1 = s.query(User, literal("x")) + q2 = s.query(User, literal_column("'y'")) + q3 = q1.union(q2) + + self.assert_compile( + q3, + "SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name, anon_1.anon_2 AS anon_1_anon_2 FROM " + "(SELECT users.id AS id, users.name AS name, :param_1 AS anon_2 FROM users " + "UNION SELECT users.id AS id, users.name AS name, 'y' FROM users) AS anon_1" + , use_default_dialect = True + ) + + q4 = s.query(User, literal_column("'x'").label('foo')) + q5 = s.query(User, literal("y")) + q6 = q4.union(q5) + + for q in (q3, q6): + eq_(q.all(), + [ + (User(id=7, name=u'jack'), u'x'), + (User(id=7, name=u'jack'), u'y'), + (User(id=8, name=u'ed'), u'x'), + (User(id=8, name=u'ed'), u'y'), + (User(id=9, name=u'fred'), u'x'), + (User(id=9, name=u'fred'), u'y'), + (User(id=10, name=u'chuck'), u'x'), + (User(id=10, name=u'chuck'), u'y') + ] + ) @testing.fails_on('mysql', "mysql doesn't support intersect") def test_intersect(self): @@ -2231,7 +2265,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): self.assert_sql_count(testing.db, go, 1) -class MixedEntitiesTest(QueryTest): +class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): def test_values(self): sess = create_session() @@ -2470,16 +2504,16 @@ class MixedEntitiesTest(QueryTest): for q in [ sess.query(Order, oalias).filter(Order.user_id==oalias.user_id).filter(Order.user_id==7).filter(Order.id>oalias.id).order_by(Order.id, oalias.id), - sess.query(Order, oalias)._from_self().filter(Order.user_id==oalias.user_id).filter(Order.user_id==7).filter(Order.id>oalias.id).order_by(Order.id, oalias.id), + sess.query(Order, oalias).from_self().filter(Order.user_id==oalias.user_id).filter(Order.user_id==7).filter(Order.id>oalias.id).order_by(Order.id, oalias.id), # same thing, but reversed. - sess.query(oalias, Order)._from_self().filter(oalias.user_id==Order.user_id).filter(oalias.user_id==7).filter(Order.idoalias.id)._from_self().order_by(Order.id, oalias.id).limit(10).options(eagerload(Order.items)), + sess.query(Order, oalias).filter(Order.user_id==oalias.user_id).filter(Order.user_id==7).filter(Order.id>oalias.id).from_self().order_by(Order.id, oalias.id).limit(10).options(eagerload(Order.items)), # gratuitous four layers - sess.query(Order, oalias).filter(Order.user_id==oalias.user_id).filter(Order.user_id==7).filter(Order.id>oalias.id)._from_self()._from_self()._from_self().order_by(Order.id, oalias.id).limit(10).options(eagerload(Order.items)), + sess.query(Order, oalias).filter(Order.user_id==oalias.user_id).filter(Order.user_id==7).filter(Order.id>oalias.id).from_self().from_self().from_self().order_by(Order.id, oalias.id).limit(10).options(eagerload(Order.items)), ]: @@ -2491,6 +2525,19 @@ class MixedEntitiesTest(QueryTest): (Order(address_id=None,description=u'order 5',isopen=0,user_id=7,id=5), Order(address_id=1,description=u'order 3',isopen=1,user_id=7,id=3)) ] ) + + + # ensure column expressions are taken from inside the subquery, not restated at the top + q = sess.query(Order.id, Order.description, literal_column("'q'").label('foo')).filter(Order.description == u'order 3').from_self() + self.assert_compile(q, + "SELECT anon_1.orders_id AS anon_1_orders_id, anon_1.orders_description AS anon_1_orders_description, " + "anon_1.foo AS anon_1_foo FROM (SELECT orders.id AS orders_id, orders.description AS orders_description, " + "'q' AS foo FROM orders WHERE orders.description = :description_1) AS anon_1", use_default_dialect=True) + eq_( + q.all(), + [(3, u'order 3', 'q')] + ) + def test_multi_mappers(self): @@ -3026,7 +3073,7 @@ class SelfReferentialTest(_base.MappedTest): sess.query(Node, parent, grandparent).\ join((Node.parent, parent), (parent.parent, grandparent)).\ filter(Node.data=='n122').filter(parent.data=='n12').\ - filter(grandparent.data=='n1')._from_self().first(), + filter(grandparent.data=='n1').from_self().first(), (Node(data='n122'), Node(data='n12'), Node(data='n1')) ) @@ -3035,7 +3082,7 @@ class SelfReferentialTest(_base.MappedTest): sess.query(parent, grandparent, Node).\ join((Node.parent, parent), (parent.parent, grandparent)).\ filter(Node.data=='n122').filter(parent.data=='n12').\ - filter(grandparent.data=='n1')._from_self().first(), + filter(grandparent.data=='n1').from_self().first(), (Node(data='n12'), Node(data='n1'), Node(data='n122')) ) @@ -3052,7 +3099,7 @@ class SelfReferentialTest(_base.MappedTest): sess.query(Node, parent, grandparent).\ join((Node.parent, parent), (parent.parent, grandparent)).\ filter(Node.data=='n122').filter(parent.data=='n12').\ - filter(grandparent.data=='n1')._from_self().\ + filter(grandparent.data=='n1').from_self().\ options(eagerload(Node.children)).first(), (Node(data='n122'), Node(data='n12'), Node(data='n1')) ) -- 2.47.2