diff options
Diffstat (limited to 'test/orm/test_froms.py')
| -rw-r--r-- | test/orm/test_froms.py | 86 |
1 files changed, 62 insertions, 24 deletions
diff --git a/test/orm/test_froms.py b/test/orm/test_froms.py index 2403f4aae..fd4bef71a 100644 --- a/test/orm/test_froms.py +++ b/test/orm/test_froms.py @@ -675,19 +675,18 @@ class AddEntityEquivalenceTest(fixtures.MappedTest, AssertsCompiledSQL): class InstancesTest(QueryTest, AssertsCompiledSQL): - def test_from_alias(self): + def test_from_alias_one(self): User, addresses, users = (self.classes.User, self.tables.addresses, self.tables.users) - - query = users.select(users.c.id==7).\ - union(users.select(users.c.id>7)).\ + query = users.select(users.c.id == 7).\ + union(users.select(users.c.id > 7)).\ alias('ulist').\ outerjoin(addresses).\ select(use_labels=True, order_by=['ulist.id', addresses.c.id]) - sess =create_session() + sess = create_session() q = sess.query(User) def go(): @@ -697,7 +696,19 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): assert self.static.user_address_result == l self.assert_sql_count(testing.db, go, 1) - sess.expunge_all() + def test_from_alias_two(self): + User, addresses, users = (self.classes.User, + self.tables.addresses, + self.tables.users) + + query = users.select(users.c.id == 7).\ + union(users.select(users.c.id > 7)).\ + alias('ulist').\ + outerjoin(addresses).\ + select(use_labels=True, + order_by=['ulist.id', addresses.c.id]) + sess = create_session() + q = sess.query(User) def go(): l = q.options(contains_alias('ulist'), @@ -706,6 +717,19 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): assert self.static.user_address_result == l self.assert_sql_count(testing.db, go, 1) + def test_from_alias_three(self): + User, addresses, users = (self.classes.User, + self.tables.addresses, + self.tables.users) + + query = users.select(users.c.id == 7).\ + union(users.select(users.c.id > 7)).\ + alias('ulist').\ + outerjoin(addresses).\ + select(use_labels=True, + order_by=['ulist.id', addresses.c.id]) + sess = create_session() + # better way. use select_entity_from() def go(): l = sess.query(User).select_entity_from(query).\ @@ -713,12 +737,19 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): assert self.static.user_address_result == l self.assert_sql_count(testing.db, go, 1) + def test_from_alias_four(self): + User, addresses, users = (self.classes.User, + self.tables.addresses, + self.tables.users) + + sess = create_session() + # same thing, but alias addresses, so that the adapter # generated by select_entity_from() is wrapped within # the adapter created by contains_eager() adalias = addresses.alias() - query = users.select(users.c.id==7).\ - union(users.select(users.c.id>7)).\ + query = users.select(users.c.id == 7).\ + union(users.select(users.c.id > 7)).\ alias('ulist').\ outerjoin(adalias).\ select(use_labels=True, @@ -902,6 +933,11 @@ class InstancesTest(QueryTest, AssertsCompiledSQL): order_by(users.c.id, oalias.c.id, ialias.c.id) # test using Alias with more than one level deep + + # new way: + #from sqlalchemy.orm.strategy_options import Load + #opt = Load(User).contains_eager('orders', alias=oalias).contains_eager('items', alias=ialias) + def go(): l = list(q.options( contains_eager('orders', alias=oalias), @@ -1781,7 +1817,6 @@ class SelectFromTest(QueryTest, AssertsCompiledSQL): users, User = self.tables.users, self.classes.User - mapper(User, users) sess = create_session() @@ -1790,21 +1825,21 @@ 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", ) self.assert_compile( - sess.query(ualias).select_entity_from(sel).filter(ualias.id>sel.c.id), + sess.query(ualias).select_entity_from(sel).filter(ualias.id > sel.c.id), "SELECT users_1.id AS users_1_id, users_1.name AS users_1_name FROM " "users AS users_1, (SELECT users.id AS id, users.name AS name FROM " "users WHERE users.id IN (:id_1, :id_2)) AS anon_1 WHERE users_1.id > anon_1.id", ) self.assert_compile( - sess.query(ualias).select_entity_from(sel).join(ualias, ualias.id>sel.c.id), + sess.query(ualias).select_entity_from(sel).join(ualias, ualias.id > sel.c.id), "SELECT users_1.id AS users_1_id, users_1.name AS users_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 " @@ -1812,29 +1847,26 @@ class SelectFromTest(QueryTest, AssertsCompiledSQL): ) self.assert_compile( - sess.query(ualias).select_entity_from(sel).join(ualias, ualias.id>User.id), + sess.query(ualias).select_entity_from(sel).join(ualias, ualias.id > User.id), "SELECT users_1.id AS users_1_id, users_1.name AS users_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 anon_1.id < users_1.id" + "JOIN users AS users_1 ON users_1.id > anon_1.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", ) - - # this one uses an explicit join(left, right, onclause) so works self.assert_compile( - sess.query(ualias).select_entity_from(join(sel, ualias, ualias.id>sel.c.id)), + sess.query(ualias).select_entity_from(join(sel, ualias, ualias.id > sel.c.id)), "SELECT users_1.id AS users_1_id, users_1.name AS users_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", - use_default_dialect=True + "IN (:id_1, :id_2)) AS anon_1 JOIN users AS users_1 ON users_1.id > anon_1.id" ) @@ -1848,25 +1880,31 @@ class SelectFromTest(QueryTest, AssertsCompiledSQL): self.assert_compile( sess.query(User).select_from(ua).join(User, ua.name > User.name), "SELECT users.id AS users_id, users.name AS users_name " - "FROM users AS users_1 JOIN users ON users.name < users_1.name" + "FROM users AS users_1 JOIN users ON users_1.name > users.name" ) self.assert_compile( sess.query(User.name).select_from(ua).join(User, ua.name > User.name), "SELECT users.name AS users_name FROM users AS users_1 " - "JOIN users ON users.name < users_1.name" + "JOIN users ON users_1.name > users.name" ) self.assert_compile( sess.query(ua.name).select_from(ua).join(User, ua.name > User.name), "SELECT users_1.name AS users_1_name FROM users AS users_1 " - "JOIN users ON users.name < users_1.name" + "JOIN users ON users_1.name > users.name" ) self.assert_compile( sess.query(ua).select_from(User).join(ua, ua.name > User.name), "SELECT users_1.id AS users_1_id, users_1.name AS users_1_name " - "FROM users JOIN users AS users_1 ON users.name < users_1.name" + "FROM users JOIN users AS users_1 ON users_1.name > users.name" + ) + + self.assert_compile( + sess.query(ua).select_from(User).join(ua, User.name > ua.name), + "SELECT users_1.id AS users_1_id, users_1.name AS users_1_name " + "FROM users JOIN users AS users_1 ON users.name > users_1.name" ) # this is tested in many other places here, just adding it |