From ee25562461db46e31643a2f4ff9c981313a52dc3 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 26 Nov 2010 16:40:41 -0500 Subject: [PATCH] - merge 2-arg form of query.join(), [ticket:1923] --- doc/build/orm/tutorial.rst | 11 +- lib/sqlalchemy/orm/query.py | 64 +++-- lib/sqlalchemy/orm/strategies.py | 2 +- test/ext/test_serializer.py | 4 +- test/orm/inheritance/test_query.py | 45 ++-- test/orm/test_eager_relations.py | 28 ++- test/orm/test_mapper.py | 2 +- test/orm/test_query.py | 365 ++++++++++++++++++----------- 8 files changed, 324 insertions(+), 197 deletions(-) diff --git a/doc/build/orm/tutorial.rst b/doc/build/orm/tutorial.rst index 7de7ac3449..de1edd9202 100644 --- a/doc/build/orm/tutorial.rst +++ b/doc/build/orm/tutorial.rst @@ -902,9 +902,9 @@ Or we can make a real JOIN construct; the most common way is to use :meth:`~sqla :meth:`~sqlalchemy.orm.query.Query.join` knows how to join between ``User`` and ``Address`` because there's only one foreign key between them. If there were no foreign keys, or several, :meth:`~sqlalchemy.orm.query.Query.join` works better when one of the following forms are used:: - query.join((Address, User.id==Address.user_id)) # explicit condition (note the tuple) + query.join(Address, User.id==Address.user_id) # explicit condition query.join(User.addresses) # specify relationship from left to right - query.join((Address, User.addresses)) # same, with explicit target + query.join(Address, User.addresses) # same, with explicit target query.join('addresses') # same, using a string Note that when :meth:`~sqlalchemy.orm.query.Query.join` is called with an explicit target as well as an ON clause, we use a tuple as the argument. This is so that multiple joins can be chained together, as in:: @@ -974,7 +974,8 @@ When querying across multiple tables, if the same table needs to be referenced m >>> adalias2 = aliased(Address) {sql}>>> for username, email1, email2 in \ ... session.query(User.name, adalias1.email_address, adalias2.email_address).\ - ... join((adalias1, User.addresses), (adalias2, User.addresses)).\ + ... join(adalias1, User.addresses).\ + ... join(adalias2, User.addresses).\ ... filter(adalias1.email_address=='jack@google.com').\ ... filter(adalias2.email_address=='j25@yahoo.com'): ... print username, email1, email2 # doctest: +NORMALIZE_WHITESPACE @@ -1007,7 +1008,7 @@ Once we have our statement, it behaves like a :class:`~sqlalchemy.schema.Table` .. sourcecode:: python+sql {sql}>>> for u, count in session.query(User, stmt.c.address_count).\ - ... outerjoin((stmt, User.id==stmt.c.user_id)).order_by(User.id): # doctest: +NORMALIZE_WHITESPACE + ... outerjoin(stmt, User.id==stmt.c.user_id).order_by(User.id): # doctest: +NORMALIZE_WHITESPACE ... print u, count SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, @@ -1031,7 +1032,7 @@ Above, we just selected a result that included a column from a subquery. What i {sql}>>> stmt = session.query(Address).filter(Address.email_address != 'j25@yahoo.com').subquery() >>> adalias = aliased(Address, stmt) - >>> for user, address in session.query(User, adalias).join((adalias, User.addresses)): # doctest: +NORMALIZE_WHITESPACE + >>> for user, address in session.query(User, adalias).join(adalias, User.addresses): # doctest: +NORMALIZE_WHITESPACE ... print user, address SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, anon_1.id AS anon_1_id, diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py index 97f058333a..1f45b58581 100644 --- a/lib/sqlalchemy/orm/query.py +++ b/lib/sqlalchemy/orm/query.py @@ -1112,7 +1112,6 @@ class Query(object): expression.except_all(*([self]+ list(q))) ) - @util.accepts_a_list_as_starargs(list_deprecation='deprecated') def join(self, *props, **kwargs): """Create a join against this ``Query`` object's criterion and apply generatively, returning the newly resulting ``Query``. @@ -1126,9 +1125,12 @@ class Query(object): * a class-mapped attribute, i.e. Houses.rooms. This will create a join from "Houses" table to that of the "rooms" relationship. - * a 2-tuple containing a target class or selectable, and an "ON" - clause. The ON clause can be the property name/ attribute like - above, or a SQL expression. + 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 supercedes the + previous "tuple" calling form - multiple join() calls should + be used for multiple (target, onclause) pairs. e.g.:: @@ -1139,7 +1141,7 @@ class Query(object): # join the Person entity to an alias of itself, # along the "friends" relationship PAlias = aliased(Person) - session.query(Person).join((Palias, Person.friends)) + session.query(Person).join(Palias, Person.friends) # join from Houses to the "rooms" attribute on the # "Colonials" subclass of Houses, then join to the @@ -1150,8 +1152,8 @@ class Query(object): # 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'), - Engineer.computers) + 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. @@ -1159,11 +1161,10 @@ class Query(object): # same thing, but spelled out entirely explicitly # including the association table. - session.query(Article).join( - (article_keywords, - Articles.id==article_keywords.c.article_id), - (Keyword, Keyword.id==article_keywords.c.keyword_id) - ) + session.query(Article).join(article_keywords, + Articles.id==article_keywords.c.article_id).\\ + join(Keyword, + Keyword.id==article_keywords.c.keyword_id) \**kwargs include: @@ -1172,10 +1173,19 @@ class Query(object): same table. Consider usage of the aliased(SomeClass) construct as a more explicit approach to this. - from_joinpoint - when joins are specified using string property - names, locate the property from the mapper found in the most - recent previous join() call, instead of from the root entity. - + 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) + """ aliased, from_joinpoint = kwargs.pop('aliased', False),\ kwargs.pop('from_joinpoint', False) @@ -1186,7 +1196,6 @@ class Query(object): outerjoin=False, create_aliases=aliased, from_joinpoint=from_joinpoint) - @util.accepts_a_list_as_starargs(list_deprecation='deprecated') def outerjoin(self, *props, **kwargs): """Create a left outer join against this ``Query`` object's criterion and apply generatively, retunring the newly resulting ``Query``. @@ -1214,16 +1223,21 @@ class Query(object): if not from_joinpoint: self._reset_joinpoint() - if len(keys) >= 2 and \ - isinstance(keys[1], expression.ClauseElement) and \ - not isinstance(keys[1], expression.FromClause): - raise sa_exc.ArgumentError( - "You appear to be passing a clause expression as the second " - "argument to query.join(). Did you mean to use the form " - "query.join((target, onclause))? Note the tuple.") - + if len(keys) == 2 and \ + isinstance(keys[0], (expression.FromClause, + type, AliasedClass)) and \ + isinstance(keys[1], (basestring, expression.ClauseElement, + interfaces.PropComparator)): + # detect 2-arg form of join and + # convert to a tuple. + keys = (keys,) + for arg1 in util.to_list(keys): if isinstance(arg1, tuple): + # "tuple" form of join, multiple + # tuples are accepted as well. The simpler + # "2-arg" form is preferred. May deprecate + # the "tuple" usage. arg1, arg2 = arg1 else: arg2 = None diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py index 8be34da240..1411216f22 100644 --- a/lib/sqlalchemy/orm/strategies.py +++ b/lib/sqlalchemy/orm/strategies.py @@ -828,7 +828,7 @@ class SubqueryLoader(AbstractRelationshipLoader): attr = key if second_to_last: - q = q.join((parent_alias, attr), from_joinpoint=True) + q = q.join(parent_alias, attr, from_joinpoint=True) else: q = q.join(attr, aliased=middle, from_joinpoint=True) diff --git a/test/ext/test_serializer.py b/test/ext/test_serializer.py index ca9de27e73..24626bc8e4 100644 --- a/test/ext/test_serializer.py +++ b/test/ext/test_serializer.py @@ -124,8 +124,8 @@ class SerializeTest(MappedTest): def test_aliases(self): u7, u8, u9, u10 = Session.query(User).order_by(User.id).all() ualias = aliased(User) - q = Session.query(User, ualias).join((ualias, User.id - < ualias.id)).filter(User.id < 9).order_by(User.id, + q = Session.query(User, ualias).join(ualias, User.id + < ualias.id).filter(User.id < 9).order_by(User.id, ualias.id) eq_(list(q.all()), [(u7, u8), (u7, u9), (u7, u10), (u8, u9), (u8, u10)]) diff --git a/test/orm/inheritance/test_query.py b/test/orm/inheritance/test_query.py index db2475761b..45b3e9da16 100644 --- a/test/orm/inheritance/test_query.py +++ b/test/orm/inheritance/test_query.py @@ -237,7 +237,7 @@ def _produce_test(select_type): e = aliased(Person) c = aliased(Company) - q = sess.query(Company, Person, c, e).join((Person, Company.employees)).join((e, c.employees)).\ + q = sess.query(Company, Person, c, e).join(Person, Company.employees).join(e, c.employees).\ filter(Person.name=='dilbert').filter(e.name=='wally') eq_(q.count(), 1) @@ -358,25 +358,25 @@ def _produce_test(select_type): ) eq_( - sess.query(Manager.name).join((Paperwork, Manager.paperwork)).order_by(Manager.name).all(), + sess.query(Manager.name).join(Paperwork, Manager.paperwork).order_by(Manager.name).all(), [(u'dogbert',), (u'dogbert',), (u'pointy haired boss',)] ) eq_( - sess.query(Person.name).join((Paperwork, Person.paperwork)).order_by(Person.name).all(), + sess.query(Person.name).join(Paperwork, Person.paperwork).order_by(Person.name).all(), [(u'dilbert',), (u'dilbert',), (u'dogbert',), (u'dogbert',), (u'pointy haired boss',), (u'vlad',), (u'wally',), (u'wally',)] ) # Load Person.name, joining from Person -> paperwork, get all # the people. eq_( - sess.query(Person.name).join((paperwork, Person.person_id==paperwork.c.person_id)).order_by(Person.name).all(), + sess.query(Person.name).join(paperwork, Person.person_id==paperwork.c.person_id).order_by(Person.name).all(), [(u'dilbert',), (u'dilbert',), (u'dogbert',), (u'dogbert',), (u'pointy haired boss',), (u'vlad',), (u'wally',), (u'wally',)] ) # same, on manager. get only managers. eq_( - sess.query(Manager.name).join((paperwork, Manager.person_id==paperwork.c.person_id)).order_by(Person.name).all(), + sess.query(Manager.name).join(paperwork, Manager.person_id==paperwork.c.person_id).order_by(Person.name).all(), [(u'dogbert',), (u'dogbert',), (u'pointy haired boss',)] ) @@ -385,41 +385,41 @@ def _produce_test(select_type): # the SQL is incorrect. assert_raises( sa_exc.DBAPIError, - sess.query(Person.name).join((paperwork, Manager.person_id==paperwork.c.person_id)).order_by(Person.name).all, + sess.query(Person.name).join(paperwork, Manager.person_id==paperwork.c.person_id).order_by(Person.name).all, ) elif select_type == 'Unions': # with the union, not something anyone would really be using here, it joins to # the full result set. This is 0.6's behavior and is more or less wrong. eq_( - sess.query(Person.name).join((paperwork, Manager.person_id==paperwork.c.person_id)).order_by(Person.name).all(), + sess.query(Person.name).join(paperwork, Manager.person_id==paperwork.c.person_id).order_by(Person.name).all(), [(u'dilbert',), (u'dilbert',), (u'dogbert',), (u'dogbert',), (u'pointy haired boss',), (u'vlad',), (u'wally',), (u'wally',)] ) else: # when a join is present and managers.person_id is available, you get the managers. eq_( - sess.query(Person.name).join((paperwork, Manager.person_id==paperwork.c.person_id)).order_by(Person.name).all(), + sess.query(Person.name).join(paperwork, Manager.person_id==paperwork.c.person_id).order_by(Person.name).all(), [(u'dogbert',), (u'dogbert',), (u'pointy haired boss',)] ) eq_( - sess.query(Manager).join((Paperwork, Manager.paperwork)).order_by(Manager.name).all(), + sess.query(Manager).join(Paperwork, Manager.paperwork).order_by(Manager.name).all(), [m1, b1] ) eq_( - sess.query(Manager.name).join((paperwork, Manager.person_id==paperwork.c.person_id)).order_by(Manager.name).all(), + sess.query(Manager.name).join(paperwork, Manager.person_id==paperwork.c.person_id).order_by(Manager.name).all(), [(u'dogbert',), (u'dogbert',), (u'pointy haired boss',)] ) eq_( - sess.query(Manager.person_id).join((paperwork, Manager.person_id==paperwork.c.person_id)).order_by(Manager.name).all(), + sess.query(Manager.person_id).join(paperwork, Manager.person_id==paperwork.c.person_id).order_by(Manager.name).all(), [(4,), (4,), (3,)] ) eq_( sess.query(Manager.name, Paperwork.description). - join((Paperwork, Manager.person_id==Paperwork.person_id)). + join(Paperwork, Manager.person_id==Paperwork.person_id). order_by(Paperwork.paperwork_id). all(), [(u'pointy haired boss', u'review #1'), (u'dogbert', u'review #2'), (u'dogbert', u'review #3')] @@ -427,7 +427,7 @@ def _produce_test(select_type): malias = aliased(Manager) eq_( - sess.query(malias.name).join((paperwork, malias.person_id==paperwork.c.person_id)).all(), + sess.query(malias.name).join(paperwork, malias.person_id==paperwork.c.person_id).all(), [(u'pointy haired boss',), (u'dogbert',), (u'dogbert',)] ) @@ -596,19 +596,19 @@ def _produce_test(select_type): def test_join_to_subclass(self): sess = create_session() - eq_(sess.query(Company).join(('employees', people.join(engineers))).filter(Engineer.primary_language=='java').all(), [c1]) + eq_(sess.query(Company).join(people.join(engineers), 'employees').filter(Engineer.primary_language=='java').all(), [c1]) if select_type == '': eq_(sess.query(Company).select_from(companies.join(people).join(engineers)).filter(Engineer.primary_language=='java').all(), [c1]) - eq_(sess.query(Company).join(('employees', people.join(engineers))).filter(Engineer.primary_language=='java').all(), [c1]) + eq_(sess.query(Company).join(people.join(engineers), 'employees').filter(Engineer.primary_language=='java').all(), [c1]) ealias = aliased(Engineer) - eq_(sess.query(Company).join(('employees', ealias)).filter(ealias.primary_language=='java').all(), [c1]) + eq_(sess.query(Company).join(ealias, 'employees').filter(ealias.primary_language=='java').all(), [c1]) eq_(sess.query(Person).select_from(people.join(engineers)).join(Engineer.machines).all(), [e1, e2, e3]) eq_(sess.query(Person).select_from(people.join(engineers)).join(Engineer.machines).filter(Machine.name.ilike("%ibm%")).all(), [e1, e3]) - eq_(sess.query(Company).join(('employees', people.join(engineers)), Engineer.machines).all(), [c1, c2]) - eq_(sess.query(Company).join(('employees', people.join(engineers)), Engineer.machines).filter(Machine.name.ilike("%thinkpad%")).all(), [c1]) + eq_(sess.query(Company).join(people.join(engineers), 'employees').join(Engineer.machines).all(), [c1, c2]) + eq_(sess.query(Company).join(people.join(engineers), 'employees').join(Engineer.machines).filter(Machine.name.ilike("%thinkpad%")).all(), [c1]) else: eq_(sess.query(Company).select_from(companies.join(people).join(engineers)).filter(Engineer.primary_language=='java').all(), [c1]) eq_(sess.query(Company).join('employees').filter(Engineer.primary_language=='java').all(), [c1]) @@ -677,10 +677,13 @@ def _produce_test(select_type): c2 ) - # same, using explicit join condition. Query.join() must adapt the on clause - # here to match the subquery wrapped around "people join engineers". + # same, using explicit join condition. Query.join() must + # adapt the on clause here to match the subquery wrapped around + # "people join engineers". eq_( - sess.query(Company).join((Engineer, Company.company_id==Engineer.company_id)).filter(Engineer.engineer_name=='vlad').one(), + sess.query(Company). + join(Engineer, Company.company_id==Engineer.company_id). + filter(Engineer.engineer_name=='vlad').one(), c2 ) diff --git a/test/orm/test_eager_relations.py b/test/orm/test_eager_relations.py index 823a6d02b6..94d1b34648 100644 --- a/test/orm/test_eager_relations.py +++ b/test/orm/test_eager_relations.py @@ -1176,8 +1176,8 @@ class AddEntityTest(_fixtures.FixtureTest): sess = create_session() oalias = sa.orm.aliased(Order) def go(): - ret = sess.query(User, oalias).join(('orders', oalias)).order_by(User.id, - oalias.id).all() + ret = sess.query(User, oalias).join(oalias, 'orders').\ + order_by(User.id,oalias.id).all() eq_(ret, self._assert_result()) self.assert_sql_count(testing.db, go, 1) @@ -1197,16 +1197,19 @@ class AddEntityTest(_fixtures.FixtureTest): oalias = sa.orm.aliased(Order) def go(): - ret = sess.query(User, oalias).options(joinedload('addresses')).join( - ('orders', oalias)).order_by(User.id, oalias.id).all() + ret = sess.query(User, oalias).options(joinedload('addresses')).\ + join(oalias, 'orders').\ + order_by(User.id, oalias.id).all() eq_(ret, self._assert_result()) self.assert_sql_count(testing.db, go, 6) sess.expunge_all() def go(): - ret = sess.query(User, oalias).options(joinedload('addresses'), - joinedload(oalias.items)).join( - ('orders', oalias)).order_by(User.id, oalias.id).all() + ret = sess.query(User, oalias).\ + options(joinedload('addresses'), + joinedload(oalias.items)).\ + join(oalias, 'orders').\ + order_by(User.id, oalias.id).all() eq_(ret, self._assert_result()) self.assert_sql_count(testing.db, go, 1) @@ -1684,8 +1687,8 @@ class MixedEntitiesTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL): ), ], sess.query(User, Order, u1, o1).\ - join((Order, User.orders)).options(joinedload(User.addresses), joinedload(Order.items)).filter(User.id==9).\ - join((o1, u1.orders)).options(joinedload(u1.addresses), joinedload(o1.items)).filter(u1.id==7).\ + join(Order, User.orders).options(joinedload(User.addresses), joinedload(Order.items)).filter(User.id==9).\ + join(o1, u1.orders).options(joinedload(u1.addresses), joinedload(o1.items)).filter(u1.id==7).\ filter(Order.id User.id)), + join(ualias, ualias.id > User.id), "SELECT users.id AS users_id, users.name AS users_name, " "users_1.id AS users_1_id, users_1.name AS users_1_name " "FROM users INNER JOIN users AS users_1 USE INDEX (col1_index,col2_index) " @@ -1670,7 +1670,7 @@ class InheritedJoinTest(_base.MappedTest, AssertsCompiledSQL): , use_default_dialect=True ) self.assert_compile( - sess.query(Company).join((Engineer, Company.company_id==Engineer.company_id)). + sess.query(Company).join(Engineer, Company.company_id==Engineer.company_id). filter(Engineer.engineer_name=='vlad'), "SELECT companies.company_id AS companies_company_id, companies.name " "AS companies_name " @@ -1696,7 +1696,7 @@ class InheritedJoinTest(_base.MappedTest, AssertsCompiledSQL): sess = create_session() self.assert_compile( - sess.query(Company).join((people.join(engineers), Company.employees)). + sess.query(Company).join(people.join(engineers), Company.employees). filter(Engineer.name=='dilbert'), "SELECT companies.company_id AS companies_company_id, companies.name AS " "companies_name " @@ -1715,8 +1715,8 @@ class InheritedJoinTest(_base.MappedTest, AssertsCompiledSQL): mach_alias = machines.select() self.assert_compile( - sess.query(Company).join((people.join(engineers), Company.employees), - (mach_alias, Engineer.machines)). + sess.query(Company).join(people.join(engineers), Company.employees). + join(mach_alias, Engineer.machines, from_joinpoint=True). filter(Engineer.name=='dilbert').filter(Machine.name=='foo'), "SELECT companies.company_id AS companies_company_id, companies.name AS " "companies_name " @@ -1871,22 +1871,46 @@ class JoinTest(QueryTest, AssertsCompiledSQL): , use_default_dialect=True ) - def test_common_mistake(self): + def test_multi_tuple_Form(self): + """test the 'tuple' form of join, now superceded by the two-element join() form. + + Not deprecating this style as of yet. + + """ + sess = create_session() - subq = sess.query(User).subquery() - assert_raises_message( - sa_exc.ArgumentError, "You appear to be passing a clause expression", - sess.query(User).join, subq, User.name==subq.c.name) - - subq = sess.query(Order).subquery() - assert_raises_message( - sa_exc.ArgumentError, "You appear to be passing a clause expression", - sess.query(User).join, subq, User.id==subq.c.user_id) + #assert_raises( + # sa.exc.SADeprecationWarning, + # sess.query(User).join, (Order, User.id==Order.user_id) + #) + + self.assert_compile( + sess.query(User).join((Order, User.id==Order.user_id)), + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users JOIN orders ON users.id = orders.user_id", + use_default_dialect=True + ) - assert_raises_message( - sa_exc.ArgumentError, "You appear to be passing a clause expression", - sess.query(User).join, Order, User.id==Order.user_id) + self.assert_compile( + sess.query(User).join( + (Order, User.id==Order.user_id), + (Item, Order.items)), + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users JOIN orders ON users.id = orders.user_id " + "JOIN order_items AS order_items_1 ON orders.id = " + "order_items_1.order_id JOIN items ON items.id = " + "order_items_1.item_id", + use_default_dialect=True + ) + + # the old "backwards" form + self.assert_compile( + sess.query(User).join(("orders", Order)), + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users JOIN orders ON users.id = orders.user_id", + use_default_dialect=True + ) def test_single_prop(self): sess = create_session() @@ -1996,8 +2020,8 @@ class JoinTest(QueryTest, AssertsCompiledSQL): ualias = aliased(User) self.assert_compile( sess.query(ualias). - join((oalias1, ualias.orders)).\ - join((Address, ualias.addresses)), + join(oalias1, ualias.orders).\ + join(Address, ualias.addresses), "SELECT users_1.id AS users_1_id, users_1.name AS " "users_1_name FROM users AS users_1 JOIN orders AS orders_1 " "ON users_1.id = orders_1.user_id JOIN addresses ON users_1.id " @@ -2009,9 +2033,9 @@ class JoinTest(QueryTest, AssertsCompiledSQL): ualias2 = aliased(User) self.assert_compile( sess.query(ualias). - join((Address, ualias.addresses)). - join((ualias2, Address.user)). - join((Order, ualias.orders)), + join(Address, ualias.addresses). + join(ualias2, Address.user). + join(Order, ualias.orders), "SELECT users_1.id AS users_1_id, users_1.name AS users_1_name FROM users " "AS users_1 JOIN addresses ON users_1.id = addresses.user_id JOIN users AS users_2 " "ON users_2.id = addresses.user_id JOIN orders ON users_1.id = orders.user_id" @@ -2035,24 +2059,37 @@ class JoinTest(QueryTest, AssertsCompiledSQL): for oalias,ialias in [(True, True), (False, False), (True, False), (False, True)]: eq_( - sess.query(User).join('orders', aliased=oalias).join('items', from_joinpoint=True, aliased=ialias).filter(Item.description == 'item 4').all(), + sess.query(User).join('orders', aliased=oalias).\ + join('items', + from_joinpoint=True, + aliased=ialias).\ + filter(Item.description == 'item 4').all(), [User(name='jack')] ) # use middle criterion eq_( - sess.query(User).join('orders', aliased=oalias).filter(Order.user_id==9).join('items', from_joinpoint=True, aliased=ialias).filter(Item.description=='item 4').all(), + sess.query(User).join('orders', aliased=oalias).\ + filter(Order.user_id==9).\ + join('items', from_joinpoint=True, + aliased=ialias).\ + filter(Item.description=='item 4').all(), [] ) orderalias = aliased(Order) itemalias = aliased(Item) eq_( - sess.query(User).join(('orders', orderalias), ('items', itemalias)).filter(itemalias.description == 'item 4').all(), + sess.query(User).join(orderalias, 'orders'). + join(itemalias, 'items', from_joinpoint=True). + filter(itemalias.description == 'item 4').all(), [User(name='jack')] ) eq_( - sess.query(User).join(('orders', orderalias), ('items', itemalias)).filter(orderalias.user_id==9).filter(itemalias.description=='item 4').all(), + sess.query(User).join(orderalias, 'orders'). + join(itemalias, 'items', from_joinpoint=True). + filter(orderalias.user_id==9).\ + filter(itemalias.description=='item 4').all(), [] ) @@ -2063,7 +2100,8 @@ class JoinTest(QueryTest, AssertsCompiledSQL): # intentionally join() with a non-existent "left" side self.assert_compile( sess.query(User.id, literal_column('foo')).join(Order.user), - "SELECT users.id AS users_id, foo FROM orders JOIN users ON users.id = orders.user_id" + "SELECT users.id AS users_id, foo FROM " + "orders JOIN users ON users.id = orders.user_id" , use_default_dialect=True ) @@ -2076,7 +2114,8 @@ class JoinTest(QueryTest, AssertsCompiledSQL): sess = create_session() eq_( - sess.query(User).join(Address.user).filter(Address.email_address=='ed@wood.com').all(), + sess.query(User).join(Address.user).\ + filter(Address.email_address=='ed@wood.com').all(), [User(id=8,name=u'ed')] ) @@ -2091,14 +2130,14 @@ class JoinTest(QueryTest, AssertsCompiledSQL): # before the error raise was added, this would silently work..... assert_raises( sa_exc.InvalidRequestError, - sess.query(User).join, (Address, Address.user), + sess.query(User).join, Address, Address.user, ) # but this one would silently fail adalias = aliased(Address) assert_raises( sa_exc.InvalidRequestError, - sess.query(User).join, (adalias, Address.user), + sess.query(User).join, adalias, Address.user, ) def test_multiple_with_aliases(self): @@ -2108,7 +2147,8 @@ class JoinTest(QueryTest, AssertsCompiledSQL): oalias1 = aliased(Order) oalias2 = aliased(Order) self.assert_compile( - sess.query(ualias).join((oalias1, ualias.orders), (oalias2, ualias.orders)).\ + sess.query(ualias).join(oalias1, ualias.orders). + join(oalias2, ualias.orders). filter(or_(oalias1.user_id==9, oalias2.user_id==7)), "SELECT users_1.id AS users_1_id, users_1.name AS users_1_name FROM users AS users_1 " "JOIN orders AS orders_1 ON users_1.id = orders_1.user_id JOIN orders AS orders_2 ON " @@ -2153,8 +2193,8 @@ class JoinTest(QueryTest, AssertsCompiledSQL): if False: self.assert_compile( sess.query(User, ualias).\ - join((oalias1, ualias.orders)).\ - join((oalias2, User.id==oalias2.user_id)).\ + join(oalias1, ualias.orders).\ + join(oalias2, User.id==oalias2.user_id).\ filter(or_(oalias1.user_id==9, oalias2.user_id==7)), "SELECT users.id AS users_id, users.name AS users_name, users_1.id AS users_1_id, users_1.name AS " "users_1_name FROM users JOIN orders AS orders_2 ON users.id = orders_2.user_id, " @@ -2224,11 +2264,13 @@ class JoinTest(QueryTest, AssertsCompiledSQL): def test_onclause_conditional_adaption(self): sess = create_session() + # this is now a very weird test, nobody should really + # be using the aliased flag in this way. self.assert_compile( - sess.query(User).join(User.orders, - (Item, - and_(Order.id==order_items.c.order_id, order_items.c.item_id==Item.id) - ),aliased=True + sess.query(User).join(User.orders, aliased=True). + join(Item, + and_(Order.id==order_items.c.order_id, order_items.c.item_id==Item.id), + from_joinpoint=True, aliased=True ), "SELECT users.id AS users_id, users.name AS users_name FROM users JOIN " "orders AS orders_1 ON users.id = orders_1.user_id JOIN items AS items_1 " @@ -2236,12 +2278,13 @@ class JoinTest(QueryTest, AssertsCompiledSQL): use_default_dialect=True ) + oalias = orders.select() self.assert_compile( - sess.query(User).join((oalias, User.orders), - (Item, - and_(Order.id==order_items.c.order_id, order_items.c.item_id==Item.id) - ), + sess.query(User).join(oalias, User.orders). + join(Item, + and_(Order.id==order_items.c.order_id, order_items.c.item_id==Item.id), + from_joinpoint=True ), "SELECT users.id AS users_id, users.name AS users_name FROM users JOIN " "(SELECT orders.id AS id, orders.user_id AS user_id, orders.address_id AS address_id, orders.description " @@ -2250,9 +2293,8 @@ class JoinTest(QueryTest, AssertsCompiledSQL): use_default_dialect=True ) - - # query.join(, aliased=True).join((target, sql_expression)) - # or: query.join(path_to_some_joined_table_mapper).join((target, sql_expression)) + # query.join(, aliased=True).join(target, sql_expression) + # or: query.join(path_to_some_joined_table_mapper).join(target, sql_expression) def test_pure_expression_error(self): sess = create_session() @@ -2279,7 +2321,8 @@ class JoinTest(QueryTest, AssertsCompiledSQL): ) eq_( - sess.query(User).join(Order, (Item, Order.items)).filter(Item.description == 'item 4').all(), + sess.query(User).join(Order).join(Item, Order.items) + .filter(Item.description == 'item 4').all(), [User(name='jack')] ) @@ -2287,30 +2330,27 @@ class JoinTest(QueryTest, AssertsCompiledSQL): sess = create_session() eq_( - sess.query(User).join( - (Order, User.id==Order.user_id), - (order_items, Order.id==order_items.c.order_id), - (Item, order_items.c.item_id==Item.id) - ).filter(Item.description == 'item 4').all(), + sess.query(User).join(Order, User.id==Order.user_id). + join(order_items, Order.id==order_items.c.order_id). + join(Item, order_items.c.item_id==Item.id). + filter(Item.description == 'item 4').all(), [User(name='jack')] ) eq_( - sess.query(User.name).join( - (Order, User.id==Order.user_id), - (order_items, Order.id==order_items.c.order_id), - (Item, order_items.c.item_id==Item.id) - ).filter(Item.description == 'item 4').all(), + sess.query(User.name).join(Order, User.id==Order.user_id). + join(order_items, Order.id==order_items.c.order_id). + join(Item, order_items.c.item_id==Item.id). + filter(Item.description == 'item 4').all(), [('jack',)] ) ualias = aliased(User) eq_( - sess.query(ualias.name).join( - (Order, ualias.id==Order.user_id), - (order_items, Order.id==order_items.c.order_id), - (Item, order_items.c.item_id==Item.id) - ).filter(Item.description == 'item 4').all(), + sess.query(ualias.name).join(Order, ualias.id==Order.user_id). + join(order_items, Order.id==order_items.c.order_id). + join(Item, order_items.c.item_id==Item.id). + filter(Item.description == 'item 4').all(), [('jack',)] ) @@ -2318,17 +2358,19 @@ class JoinTest(QueryTest, AssertsCompiledSQL): # the onclause must be aliased against the query's custom # FROM object eq_( - sess.query(User).order_by(User.id).offset(2).from_self().join( - (Order, User.id==Order.user_id) - ).all(), + sess.query(User).order_by(User.id).offset(2). + from_self(). + join(Order, User.id==Order.user_id). + all(), [User(name='fred')] ) # same with an explicit select_from() eq_( - sess.query(User).select_from(select([users]).order_by(User.id).offset(2).alias()).join( - (Order, User.id==Order.user_id) - ).all(), + sess.query(User).select_from(select([users]). + order_by(User.id).offset(2).alias()). + join(Order, User.id==Order.user_id). + all(), [User(name='fred')] ) @@ -2371,6 +2413,39 @@ class JoinTest(QueryTest, AssertsCompiledSQL): q = sess.query(User, AdAlias).select_from(join(AdAlias, User, AdAlias.user)).filter(User.name=='ed') eq_(l.all(), [(user8, address2),(user8, address3),(user8, address4),]) + + def test_expression_onclauses(self): + sess = create_session() + + subq = sess.query(User).subquery() + + self.assert_compile( + sess.query(User).join(subq, User.name==subq.c.name), + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users JOIN (SELECT users.id AS id, users.name " + "AS name FROM users) AS anon_1 ON users.name = anon_1.name", + use_default_dialect=True + ) + + + subq = sess.query(Order).subquery() + self.assert_compile( + sess.query(User).join(subq, User.id==subq.c.user_id), + "SELECT users.id AS users_id, users.name AS users_name FROM " + "users JOIN (SELECT orders.id AS id, orders.user_id AS user_id, " + "orders.address_id AS address_id, orders.description AS " + "description, orders.isopen AS isopen FROM orders) AS " + "anon_1 ON users.id = anon_1.user_id", + use_default_dialect=True + ) + + self.assert_compile( + sess.query(User).join(Order, User.id==Order.user_id), + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users JOIN orders ON users.id = orders.user_id", + use_default_dialect=True + ) + def test_implicit_joins_from_aliases(self): sess = create_session() @@ -2387,7 +2462,10 @@ class JoinTest(QueryTest, AssertsCompiledSQL): ) eq_( - sess.query(User, OrderAlias, Item.description).join(('orders', OrderAlias), 'items').filter_by(description='item 3').\ + sess.query(User, OrderAlias, Item.description). + join(OrderAlias, 'orders'). + join('items', from_joinpoint=True). + filter_by(description='item 3').\ order_by(User.id, OrderAlias.id).all(), [ (User(name=u'jack',id=7), Order(address_id=1,description=u'order 1',isopen=0,user_id=7,id=1), u'item 3'), @@ -2442,7 +2520,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): unioned = first.union(second) subquery = session.query(User.id).subquery() join = subquery, subquery.c.id == User.id - joined = unioned.outerjoin(join) + joined = unioned.outerjoin(*join) self.assert_compile(joined, 'SELECT anon_1.users_id AS ' 'anon_1_users_id, anon_1.users_name AS ' @@ -2460,7 +2538,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): unioned = first.union(second) subquery = session.query(User.id).subquery() join = subquery, subquery.c.id == User.id - joined = unioned.outerjoin(join) + joined = unioned.outerjoin(*join) self.assert_compile(joined, 'SELECT anon_1.users_id AS anon_1_users_id ' 'FROM (SELECT users.id AS users_id FROM ' @@ -2545,7 +2623,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): sess = create_session() eq_( - sess.query(User.name).join((addresses, User.id==addresses.c.user_id)).order_by(User.id).all(), + sess.query(User.name).join(addresses, User.id==addresses.c.user_id).order_by(User.id).all(), [(u'jack',), (u'ed',), (u'ed',), (u'ed',), (u'fred',)] ) @@ -2583,7 +2661,7 @@ class JoinTest(QueryTest, AssertsCompiledSQL): # here, the join really wants to add a second FROM clause # for "Item". but select_from disallows that self.assert_compile( - sess.query(Item.id).select_from(User).join((Item, User.id==Item.id)), + sess.query(Item.id).select_from(User).join(Item, User.id==Item.id), "SELECT items.id AS items_id FROM users JOIN items ON users.id = items.id", use_default_dialect=True ) @@ -2785,7 +2863,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): selectquery = users.outerjoin(adalias).select(use_labels=True, order_by=[users.c.id, adalias.c.id]) sess = create_session() q = sess.query(User) - + # string alias name def go(): l = list(q.options(contains_eager('addresses', alias="adalias")).instances(selectquery.execute())) @@ -2804,7 +2882,7 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): # Aliased object adalias = aliased(Address) def go(): - l = q.options(contains_eager('addresses', alias=adalias)).outerjoin((adalias, User.addresses)).order_by(User.id, adalias.id) + l = q.options(contains_eager('addresses', alias=adalias)).outerjoin(adalias, User.addresses).order_by(User.id, adalias.id) assert self.static.user_address_result == l.all() self.assert_sql_count(testing.db, go, 1) sess.expunge_all() @@ -2832,8 +2910,10 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): oalias = aliased(Order) ialias = aliased(Item) def go(): - l = q.options(contains_eager(User.orders, alias=oalias), contains_eager(User.orders, Order.items, alias=ialias)).\ - outerjoin((oalias, User.orders), (ialias, oalias.items)).order_by(User.id, oalias.id, ialias.id) + l = q.options(contains_eager(User.orders, alias=oalias), + contains_eager(User.orders, Order.items, alias=ialias)).\ + outerjoin(oalias, User.orders).\ + outerjoin(ialias, oalias.items).order_by(User.id, oalias.id, ialias.id) assert self.static.user_order_result == l.all() self.assert_sql_count(testing.db, go, 1) sess.expunge_all() @@ -2843,33 +2923,47 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): q = sess.query(User) def go(): - # outerjoin to User.orders, offset 1/limit 2 so we get user 7 + second two orders. - # then joinedload the addresses. User + Order columns go into the subquery, address - # left outer joins to the subquery, joinedloader for User.orders applies context.adapter - # to result rows. This was [ticket:1180]. - l = q.outerjoin(User.orders).options(joinedload(User.addresses), contains_eager(User.orders)).order_by(User.id, Order.id).offset(1).limit(2).all() + # outerjoin to User.orders, offset 1/limit 2 so we get user + # 7 + second two orders. then joinedload the addresses. + # User + Order columns go into the subquery, address left + # outer joins to the subquery, joinedloader for User.orders + # applies context.adapter to result rows. This was + # [ticket:1180]. + + l = \ + q.outerjoin(User.orders).options(joinedload(User.addresses), + contains_eager(User.orders)).order_by(User.id, + Order.id).offset(1).limit(2).all() eq_(l, [User(id=7, - addresses=[Address(email_address=u'jack@bean.com',user_id=7,id=1)], - name=u'jack', - orders=[ - Order(address_id=1,user_id=7,description=u'order 3',isopen=1,id=3), - Order(address_id=None,user_id=7,description=u'order 5',isopen=0,id=5) - ])]) + addresses=[Address(email_address=u'jack@bean.com', + user_id=7, id=1)], name=u'jack', + orders=[Order(address_id=1, user_id=7, + description=u'order 3', isopen=1, id=3), + Order(address_id=None, user_id=7, description=u'order 5' + , isopen=0, id=5)])]) + self.assert_sql_count(testing.db, go, 1) sess.expunge_all() def go(): - # same as above, except Order is aliased, so two adapters are applied by the - # eager loader + + # same as above, except Order is aliased, so two adapters + # are applied by the eager loader + oalias = aliased(Order) - l = q.outerjoin((User.orders, oalias)).options(joinedload(User.addresses), contains_eager(User.orders, alias=oalias)).order_by(User.id, oalias.id).offset(1).limit(2).all() + l = q.outerjoin(oalias, User.orders).\ + options(joinedload(User.addresses), + contains_eager(User.orders, alias=oalias)).\ + order_by(User.id, oalias.id).\ + offset(1).limit(2).all() eq_(l, [User(id=7, - addresses=[Address(email_address=u'jack@bean.com',user_id=7,id=1)], - name=u'jack', - orders=[ - Order(address_id=1,user_id=7,description=u'order 3',isopen=1,id=3), - Order(address_id=None,user_id=7,description=u'order 5',isopen=0,id=5) - ])]) + addresses=[Address(email_address=u'jack@bean.com', + user_id=7, id=1)], name=u'jack', + orders=[Order(address_id=1, user_id=7, + description=u'order 3', isopen=1, id=3), + Order(address_id=None, user_id=7, description=u'order 5' + , isopen=0, id=5)])]) + self.assert_sql_count(testing.db, go, 1) @@ -2909,7 +3003,7 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): eq_(list(q2), [(u'ed', u'ed@wood.com'), (u'ed', u'ed@lala.com')]) adalias = aliased(Address) - q2 = q.join(('addresses', adalias)).\ + q2 = q.join(adalias, 'addresses').\ filter(User.name.like('%e%')).\ values(User.name, adalias.email_address) eq_(list(q2), [(u'ed', u'ed@wood.com'), (u'ed', u'ed@bettyboop.com'), @@ -3098,14 +3192,14 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): adalias = aliased(Address) eq_(sess.query(User, func.count(adalias.email_address)).\ - outerjoin(('addresses', adalias)).group_by(User).\ + outerjoin(adalias, 'addresses').group_by(User).\ order_by(User.id).all(), [(User(name='jack',id=7), 1), (User(name='ed',id=8), 3), (User(name='fred',id=9), 1), (User(name='chuck',id=10), 0)] ) eq_(sess.query(func.count(adalias.email_address), User).\ - outerjoin((User.addresses, adalias)).group_by(User).\ + outerjoin(adalias, User.addresses).group_by(User).\ order_by(User.id).all(), [(1, User(name=u'jack',id=7)), (3, User(name=u'ed',id=8)), (1, User(name=u'fred',id=9)), (0, User(name=u'chuck',id=10))] @@ -3114,7 +3208,7 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): # select from aliasing + explicit aliasing eq_( sess.query(User, adalias.email_address, adalias.id).\ - outerjoin((User.addresses, adalias)).\ + outerjoin(adalias, User.addresses).\ from_self(User, adalias.email_address).\ order_by(User.id, adalias.id).all(), [ @@ -3141,11 +3235,11 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): # test eager aliasing, with/without select_from aliasing for q in [ sess.query(User, adalias.email_address).\ - outerjoin((User.addresses, adalias)).\ + outerjoin(adalias, User.addresses).\ options(joinedload(User.addresses)).\ order_by(User.id, adalias.id).limit(10), sess.query(User, adalias.email_address, adalias.id).\ - outerjoin((User.addresses, adalias)).\ + outerjoin(adalias, User.addresses).\ from_self(User, adalias.email_address).\ options(joinedload(User.addresses)).\ order_by(User.id, adalias.id).limit(10), @@ -3275,22 +3369,23 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): for address_entity in (Address, aliased(Address)): q = sess.query(User).add_entity(address_entity).\ - outerjoin(('addresses', address_entity)).\ + outerjoin(address_entity, 'addresses').\ order_by(User.id, address_entity.id) eq_(q.all(), expected) sess.expunge_all() q = sess.query(User).add_entity(address_entity) - q = q.join(('addresses', address_entity)).filter_by(email_address='ed@bettyboop.com') + q = q.join(address_entity, 'addresses') + q = q.filter_by(email_address='ed@bettyboop.com') eq_(q.all(), [(user8, address3)]) sess.expunge_all() - q = sess.query(User, address_entity).join(('addresses', address_entity)).\ + q = sess.query(User, address_entity).join(address_entity, 'addresses').\ filter_by(email_address='ed@bettyboop.com') eq_(q.all(), [(user8, address3)]) sess.expunge_all() - q = sess.query(User, address_entity).join(('addresses', address_entity)).\ + q = sess.query(User, address_entity).join(address_entity, 'addresses').\ options(joinedload('addresses')).\ filter_by(email_address='ed@bettyboop.com') eq_(list(util.OrderedSet(q.all())), [(user8, address3)]) @@ -3379,7 +3474,7 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): adalias = aliased(Address) q = sess.query(User) - q = q.group_by(users).order_by(User.id).outerjoin(('addresses', adalias)).\ + q = q.group_by(users).order_by(User.id).outerjoin(adalias, 'addresses').\ add_column(func.count(adalias.id).label('count')) eq_(q.all(), expected) sess.expunge_all() @@ -3404,7 +3499,7 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): adalias = addresses.alias() q = create_session().query(User).add_column(func.count(adalias.c.id))\ - .add_column(("Name:" + users.c.name)).outerjoin(('addresses', adalias))\ + .add_column(("Name:" + users.c.name)).outerjoin(adalias, 'addresses')\ .group_by(users).order_by(users.c.id) assert q.all() == expected @@ -3436,7 +3531,7 @@ class MixedEntitiesTest(QueryTest, AssertsCompiledSQL): sess.expunge_all() q = create_session().query(User).add_column(func.count(adalias.c.id))\ - .add_column(("Name:" + users.c.name)).outerjoin(('addresses', adalias))\ + .add_column(("Name:" + users.c.name)).outerjoin(adalias, 'addresses')\ .group_by(users).order_by(users.c.id) assert q.all() == expected @@ -3594,7 +3689,7 @@ class SelectFromTest(QueryTest, AssertsCompiledSQL): ualias = aliased(User) self.assert_compile( - sess.query(User).join((sel, User.id>sel.c.id)), + sess.query(User).join(sel, User.id>sel.c.id), "SELECT users.id AS users_id, users.name AS users_name FROM " "users JOIN (SELECT users.id AS id, users.name AS name FROM " "users WHERE users.id IN (:id_1, :id_2)) AS anon_1 ON users.id > anon_1.id", @@ -3613,16 +3708,16 @@ class SelectFromTest(QueryTest, AssertsCompiledSQL): # error is raised. join() deals with entities, not what's in # select_from(). assert_raises(sa_exc.InvalidRequestError, - sess.query(ualias).select_from(sel).join, (ualias, ualias.id>sel.c.id) + sess.query(ualias).select_from(sel).join, ualias, ualias.id>sel.c.id ) assert_raises(sa_exc.InvalidRequestError, - sess.query(ualias).select_from(sel).join, (ualias, ualias.id>User.id) + sess.query(ualias).select_from(sel).join, ualias, ualias.id>User.id ) salias = aliased(User, sel) self.assert_compile( - sess.query(salias).join((ualias, ualias.id>salias.id)), + sess.query(salias).join(ualias, ualias.id>salias.id), "SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name FROM " "(SELECT users.id AS id, users.name AS name FROM users WHERE users.id " "IN (:id_1, :id_2)) AS anon_1 JOIN users AS users_1 ON users_1.id > anon_1.id", @@ -3673,7 +3768,7 @@ class SelectFromTest(QueryTest, AssertsCompiledSQL): ) adalias = aliased(Address) - eq_(sess.query(User).select_from(sel).join(('addresses', adalias)). + eq_(sess.query(User).select_from(sel).join(adalias, 'addresses'). add_entity(adalias).order_by(User.id).order_by(adalias.id).all(), [ (User(name='jack',id=7), Address(user_id=7,email_address='jack@bean.com',id=1)), @@ -3881,14 +3976,14 @@ class SelfRefMixedTest(_base.MappedTest, AssertsCompiledSQL): n1 = aliased(Node) self.assert_compile( - sess.query(Node).join((n1, Node.children)).join((Sub, n1.subs)), + sess.query(Node).join(n1, Node.children).join(Sub, n1.subs), "SELECT nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id " "FROM nodes JOIN nodes AS nodes_1 ON nodes.id = nodes_1.parent_id " "JOIN sub_table ON nodes_1.id = sub_table.node_id" ) self.assert_compile( - sess.query(Node).join((n1, Node.children)).join((Sub, Node.subs)), + sess.query(Node).join(n1, Node.children).join(Sub, Node.subs), "SELECT nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id " "FROM nodes JOIN nodes AS nodes_1 ON nodes.id = nodes_1.parent_id " "JOIN sub_table ON nodes.id = sub_table.node_id" @@ -3900,7 +3995,7 @@ class SelfRefMixedTest(_base.MappedTest, AssertsCompiledSQL): n1 = aliased(Node) self.assert_compile( - sess.query(Node).join((n1, Node.assoc)).join((Sub, n1.subs)), + sess.query(Node).join(n1, Node.assoc).join(Sub, n1.subs), "SELECT nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id " "FROM nodes JOIN assoc_table AS assoc_table_1 ON nodes.id = " "assoc_table_1.left_id JOIN nodes AS nodes_1 ON nodes_1.id = " @@ -3908,7 +4003,7 @@ class SelfRefMixedTest(_base.MappedTest, AssertsCompiledSQL): ) self.assert_compile( - sess.query(Node).join((n1, Node.assoc)).join((Sub, Node.subs)), + sess.query(Node).join(n1, Node.assoc).join(Sub, Node.subs), "SELECT nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id " "FROM nodes JOIN assoc_table AS assoc_table_1 ON nodes.id = " "assoc_table_1.left_id JOIN nodes AS nodes_1 ON nodes_1.id = " @@ -4048,7 +4143,8 @@ class SelfReferentialTest(_base.MappedTest, AssertsCompiledSQL): parent = aliased(Node) grandparent = aliased(Node) q = sess.query(Node, parent, grandparent).\ - join((Node.parent, parent), (parent.parent, grandparent)).\ + join(parent, Node.parent).\ + join(grandparent, parent.parent).\ filter(Node.data=='n122').filter(parent.data=='n12').\ filter(grandparent.data=='n1').from_self().limit(1) @@ -4107,7 +4203,7 @@ class SelfReferentialTest(_base.MappedTest, AssertsCompiledSQL): ) self.assert_compile( - sess.query(Node).join((n1, Node.children)).join((n2, n1.children)), + sess.query(Node).join(n1, Node.children).join(n2, n1.children), "SELECT nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, nodes.data AS " "nodes_data FROM nodes JOIN nodes AS nodes_1 ON nodes.id = nodes_1.parent_id " "JOIN nodes AS nodes_2 ON nodes_1.id = nodes_2.parent_id", @@ -4115,7 +4211,7 @@ class SelfReferentialTest(_base.MappedTest, AssertsCompiledSQL): ) self.assert_compile( - sess.query(Node).join((n1, Node.children)).join((n2, Node.children)), + sess.query(Node).join(n1, Node.children).join(n2, Node.children), "SELECT nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, nodes.data AS " "nodes_data FROM nodes JOIN nodes AS nodes_1 ON nodes.id = nodes_1.parent_id " "JOIN nodes AS nodes_2 ON nodes.id = nodes_2.parent_id", @@ -4157,7 +4253,7 @@ class SelfReferentialTest(_base.MappedTest, AssertsCompiledSQL): # explicit (new syntax) eq_( - sess.query(n1).join((Node, n1.parent)).filter(Node.data=='n1').all(), + sess.query(n1).join(Node, n1.parent).filter(Node.data=='n1').all(), [Node(parent_id=1,data=u'n11',id=2), Node(parent_id=1,data=u'n12',id=3), Node(parent_id=1,data=u'n13',id=4)] ) @@ -4170,7 +4266,8 @@ class SelfReferentialTest(_base.MappedTest, AssertsCompiledSQL): grandparent = aliased(Node) eq_( sess.query(Node, parent, grandparent).\ - join((Node.parent, parent), (parent.parent, grandparent)).\ + join(parent, Node.parent).\ + join(grandparent, parent.parent).\ filter(Node.data=='n122').filter(parent.data=='n12').\ filter(grandparent.data=='n1').first(), (Node(data='n122'), Node(data='n12'), Node(data='n1')) @@ -4178,7 +4275,8 @@ class SelfReferentialTest(_base.MappedTest, AssertsCompiledSQL): eq_( sess.query(Node, parent, grandparent).\ - join((Node.parent, parent), (parent.parent, grandparent)).\ + join(parent, Node.parent).\ + join(grandparent, parent.parent).\ filter(Node.data=='n122').filter(parent.data=='n12').\ filter(grandparent.data=='n1').from_self().first(), (Node(data='n122'), Node(data='n12'), Node(data='n1')) @@ -4187,7 +4285,8 @@ class SelfReferentialTest(_base.MappedTest, AssertsCompiledSQL): # same, change order around eq_( sess.query(parent, grandparent, Node).\ - join((Node.parent, parent), (parent.parent, grandparent)).\ + join(parent, Node.parent).\ + join(grandparent, parent.parent).\ filter(Node.data=='n122').filter(parent.data=='n12').\ filter(grandparent.data=='n1').from_self().first(), (Node(data='n12'), Node(data='n1'), Node(data='n122')) @@ -4195,7 +4294,8 @@ class SelfReferentialTest(_base.MappedTest, AssertsCompiledSQL): eq_( sess.query(Node, parent, grandparent).\ - join((Node.parent, parent), (parent.parent, grandparent)).\ + join(parent, Node.parent).\ + join(grandparent, parent.parent).\ filter(Node.data=='n122').filter(parent.data=='n12').\ filter(grandparent.data=='n1').\ options(joinedload(Node.children)).first(), @@ -4204,7 +4304,8 @@ class SelfReferentialTest(_base.MappedTest, AssertsCompiledSQL): eq_( sess.query(Node, parent, grandparent).\ - join((Node.parent, parent), (parent.parent, grandparent)).\ + join(parent, Node.parent).\ + join(grandparent, parent.parent).\ filter(Node.data=='n122').filter(parent.data=='n12').\ filter(grandparent.data=='n1').from_self().\ options(joinedload(Node.children)).first(), @@ -4379,13 +4480,13 @@ class ExternalColumnsTest(QueryTest): ualias = aliased(User) eq_( - sess.query(Address, ualias).join(('user', ualias)).all(), + sess.query(Address, ualias).join(ualias, 'user').all(), [(address, address.user) for address in address_result] ) eq_( sess.query(Address, ualias.count).\ - join(('user', ualias)).\ + join(ualias, 'user').\ join('user', aliased=True).\ order_by(Address.id).all(), [ @@ -4397,7 +4498,9 @@ class ExternalColumnsTest(QueryTest): ] ) - eq_(sess.query(Address, ualias.concat, ualias.count).join(('user', ualias)).join('user', aliased=True).order_by(Address.id).all(), + eq_(sess.query(Address, ualias.concat, ualias.count). + join(ualias, 'user'). + join('user', aliased=True).order_by(Address.id).all(), [ (Address(id=1), 14, 1), (Address(id=2), 16, 3), -- 2.47.2