66
68
configure_mappers()
70
class QueryCorrelatesLikeSelect(QueryTest, AssertsCompiledSQL):
72
query_correlated = "SELECT users.name AS users_name, " \
73
"(SELECT count(addresses.id) AS count_1 FROM addresses " \
74
"WHERE addresses.user_id = users.id) AS anon_1 FROM users"
76
query_not_correlated = "SELECT users.name AS users_name, " \
77
"(SELECT count(addresses.id) AS count_1 FROM addresses, users " \
78
"WHERE addresses.user_id = users.id) AS anon_1 FROM users"
80
def test_as_scalar_select_auto_correlate(self):
81
addresses, users = self.tables.addresses, self.tables.users
83
[func.count(addresses.c.id)],
84
addresses.c.user_id==users.c.id
86
query = select([users.c.name.label('users_name'), query])
87
self.assert_compile(query, self.query_correlated,
88
dialect=default.DefaultDialect()
91
def test_as_scalar_select_explicit_correlate(self):
92
addresses, users = self.tables.addresses, self.tables.users
94
[func.count(addresses.c.id)],
95
addresses.c.user_id==users.c.id
96
).correlate(users).as_scalar()
97
query = select([users.c.name.label('users_name'), query])
98
self.assert_compile(query, self.query_correlated,
99
dialect=default.DefaultDialect()
102
def test_as_scalar_select_correlate_off(self):
103
addresses, users = self.tables.addresses, self.tables.users
105
[func.count(addresses.c.id)],
106
addresses.c.user_id==users.c.id
107
).correlate(None).as_scalar()
108
query = select([ users.c.name.label('users_name'), query])
109
self.assert_compile(query, self.query_not_correlated,
110
dialect=default.DefaultDialect()
113
def test_as_scalar_query_auto_correlate(self):
114
sess = create_session()
115
Address, User = self.classes.Address, self.classes.User
116
query = sess.query(func.count(Address.id))\
117
.filter(Address.user_id==User.id)\
119
query = sess.query(User.name, query)
120
self.assert_compile(query, self.query_correlated,
121
dialect=default.DefaultDialect()
124
def test_as_scalar_query_explicit_correlate(self):
125
sess = create_session()
126
Address, User = self.classes.Address, self.classes.User
127
query = sess.query(func.count(Address.id))\
128
.filter(Address.user_id==User.id)\
129
.correlate(self.tables.users)\
131
query = sess.query(User.name, query)
132
self.assert_compile(query, self.query_correlated,
133
dialect=default.DefaultDialect()
136
def test_as_scalar_query_correlate_off(self):
137
sess = create_session()
138
Address, User = self.classes.Address, self.classes.User
139
query = sess.query(func.count(Address.id))\
140
.filter(Address.user_id==User.id)\
143
query = sess.query(User.name, query)
144
self.assert_compile(query, self.query_not_correlated,
145
dialect=default.DefaultDialect()
69
149
class RawSelectTest(QueryTest, AssertsCompiledSQL):
70
"""compare a bunch of select() tests with the equivalent Query using straight table/columns.
150
"""compare a bunch of select() tests with the equivalent Query using
151
straight table/columns.
72
Results should be the same as Query should act as a select() pass-thru for ClauseElement entities.
153
Results should be the same as Query should act as a select() pass-
154
thru for ClauseElement entities.
157
__dialect__ = 'default'
75
159
def test_select(self):
76
160
addresses, users = self.tables.addresses, self.tables.users
78
162
sess = create_session()
80
self.assert_compile(sess.query(users).select_from(users.select()).with_labels().statement,
164
self.assert_compile(sess.query(users).select_from(
165
users.select()).with_labels().statement,
81
166
"SELECT users.id AS users_id, users.name AS users_name FROM users, "
82
167
"(SELECT users.id AS id, users.name AS name FROM users) AS anon_1",
83
dialect=default.DefaultDialect()
86
self.assert_compile(sess.query(users, exists([1], from_obj=addresses)).with_labels().statement,
170
self.assert_compile(sess.query(users, exists([1], from_obj=addresses)
171
).with_labels().statement,
87
172
"SELECT users.id AS users_id, users.name AS users_name, EXISTS "
88
173
"(SELECT 1 FROM addresses) AS anon_1 FROM users",
89
dialect=default.DefaultDialect()
92
# a little tedious here, adding labels to work around Query's auto-labelling.
93
# also correlate needed explicitly. hmmm.....
94
# TODO: can we detect only one table in the "froms" and then turn off use_labels ?
95
s = sess.query(addresses.c.id.label('id'), addresses.c.email_address.label('email')).\
96
filter(addresses.c.user_id==users.c.id).correlate(users).statement.alias()
176
# a little tedious here, adding labels to work around Query's
178
s = sess.query(addresses.c.id.label('id'),
179
addresses.c.email_address.label('email')).\
180
filter(addresses.c.user_id == users.c.id).correlate(users).\
98
self.assert_compile(sess.query(users, s.c.email).select_from(users.join(s, s.c.id==users.c.id)).with_labels().statement,
99
"SELECT users.id AS users_id, users.name AS users_name, anon_1.email AS anon_1_email "
100
"FROM users JOIN (SELECT addresses.id AS id, addresses.email_address AS email FROM addresses "
101
"WHERE addresses.user_id = users.id) AS anon_1 ON anon_1.id = users.id",
102
dialect=default.DefaultDialect()
183
self.assert_compile(sess.query(users, s.c.email).select_from(
184
users.join(s, s.c.id == users.c.id)
185
).with_labels().statement,
186
"SELECT users.id AS users_id, users.name AS users_name, "
187
"anon_1.email AS anon_1_email "
188
"FROM users JOIN (SELECT addresses.id AS id, "
189
"addresses.email_address AS email FROM addresses, users "
190
"WHERE addresses.user_id = users.id) AS anon_1 "
191
"ON anon_1.id = users.id",
105
194
x = func.lala(users.c.id).label('foo')
106
self.assert_compile(sess.query(x).filter(x==5).statement,
107
"SELECT lala(users.id) AS foo FROM users WHERE lala(users.id) = :param_1", dialect=default.DefaultDialect())
195
self.assert_compile(sess.query(x).filter(x == 5).statement,
196
"SELECT lala(users.id) AS foo FROM users WHERE "
197
"lala(users.id) = :param_1")
109
199
self.assert_compile(sess.query(func.sum(x).label('bar')).statement,
110
"SELECT sum(lala(users.id)) AS bar FROM users", dialect=default.DefaultDialect())
200
"SELECT sum(lala(users.id)) AS bar FROM users")
113
203
class FromSelfTest(QueryTest, AssertsCompiledSQL):
1842
def test_aliased_class_vs_nonaliased(self):
1843
User, users = self.classes.User, self.tables.users
1848
sess = create_session()
1849
self.assert_compile(
1850
sess.query(User).select_from(ua).join(User, ua.name > User.name),
1851
"SELECT users.id AS users_id, users.name AS users_name "
1852
"FROM users AS users_1 JOIN users ON users.name < users_1.name"
1855
self.assert_compile(
1856
sess.query(User.name).select_from(ua).join(User, ua.name > User.name),
1857
"SELECT users.name AS users_name FROM users AS users_1 "
1858
"JOIN users ON users.name < users_1.name"
1861
self.assert_compile(
1862
sess.query(ua.name).select_from(ua).join(User, ua.name > User.name),
1863
"SELECT users_1.name AS users_1_name FROM users AS users_1 "
1864
"JOIN users ON users.name < users_1.name"
1867
self.assert_compile(
1868
sess.query(ua).select_from(User).join(ua, ua.name > User.name),
1869
"SELECT users_1.id AS users_1_id, users_1.name AS users_1_name "
1870
"FROM users JOIN users AS users_1 ON users.name < users_1.name"
1873
# this is tested in many other places here, just adding it
1874
# here for comparison
1875
self.assert_compile(
1876
sess.query(User.name).\
1877
select_from(users.select().where(users.c.id > 5)),
1878
"SELECT anon_1.name AS anon_1_name FROM (SELECT users.id AS id, "
1879
"users.name AS name FROM users WHERE users.id > :id_1) AS anon_1"
1725
1882
def test_join_no_order_by(self):
1726
1883
User, users = self.classes.User, self.tables.users
2189
2346
filter(Sub1.id==1).one(),
2350
class LabelCollideTest(fixtures.MappedTest):
2351
"""Test handling for a label collision. This collision
2352
is handled by core, see ticket:2702 as well as
2353
test/sql/test_selectable->WithLabelsTest. here we want
2354
to make sure the end result is as we expect.
2359
def define_tables(cls, metadata):
2360
Table('foo', metadata,
2361
Column('id', Integer, primary_key=True),
2362
Column('bar_id', Integer)
2364
Table('foo_bar', metadata,
2365
Column('id', Integer, primary_key=True),
2369
def setup_classes(cls):
2370
class Foo(cls.Basic):
2372
class Bar(cls.Basic):
2376
def setup_mappers(cls):
2377
mapper(cls.classes.Foo, cls.tables.foo)
2378
mapper(cls.classes.Bar, cls.tables.foo_bar)
2381
def insert_data(cls):
2384
cls.classes.Foo(id=1, bar_id=2),
2385
cls.classes.Bar(id=3)
2389
def test_overlap_plain(self):
2391
row = s.query(self.classes.Foo, self.classes.Bar).all()[0]
2394
eq_(row.Foo.bar_id, 2)
2396
# all three columns are loaded independently without
2397
# overlap, no additional SQL to load all attributes
2398
self.assert_sql_count(testing.db, go, 0)
2400
def test_overlap_subquery(self):
2402
row = s.query(self.classes.Foo, self.classes.Bar).from_self().all()[0]
2405
eq_(row.Foo.bar_id, 2)
2407
# all three columns are loaded independently without
2408
# overlap, no additional SQL to load all attributes
2409
self.assert_sql_count(testing.db, go, 0)
b'\\ No newline at end of file'