(self)
| 1361 | ) |
| 1362 | |
| 1363 | def test_where_subquery(self): |
| 1364 | s = ( |
| 1365 | select(addresses.c.street) |
| 1366 | .where(addresses.c.user_id == users.c.user_id) |
| 1367 | .alias("s") |
| 1368 | ) |
| 1369 | |
| 1370 | # don't correlate in a FROM list |
| 1371 | self.assert_compile( |
| 1372 | select(users, s.c.street).select_from(s), |
| 1373 | "SELECT users.user_id, users.user_name, users.password, s.street " |
| 1374 | "FROM (SELECT addresses.street AS street FROM addresses, users " |
| 1375 | "WHERE addresses.user_id = users.user_id) AS s, users", |
| 1376 | ) |
| 1377 | self.assert_compile( |
| 1378 | table1.select().where( |
| 1379 | table1.c.myid |
| 1380 | == select(table1.c.myid) |
| 1381 | .where(table1.c.name == "jack") |
| 1382 | .scalar_subquery() |
| 1383 | ), |
| 1384 | "SELECT mytable.myid, mytable.name, " |
| 1385 | "mytable.description FROM mytable WHERE " |
| 1386 | "mytable.myid = (SELECT mytable.myid FROM " |
| 1387 | "mytable WHERE mytable.name = :name_1)", |
| 1388 | ) |
| 1389 | self.assert_compile( |
| 1390 | table1.select().where( |
| 1391 | table1.c.myid |
| 1392 | == select(table2.c.otherid) |
| 1393 | .where(table1.c.name == table2.c.othername) |
| 1394 | .scalar_subquery() |
| 1395 | ), |
| 1396 | "SELECT mytable.myid, mytable.name, " |
| 1397 | "mytable.description FROM mytable WHERE " |
| 1398 | "mytable.myid = (SELECT " |
| 1399 | "myothertable.otherid FROM myothertable " |
| 1400 | "WHERE mytable.name = myothertable.othernam" |
| 1401 | "e)", |
| 1402 | ) |
| 1403 | self.assert_compile( |
| 1404 | table1.select().where( |
| 1405 | exists(1).where(table2.c.otherid == table1.c.myid) |
| 1406 | ), |
| 1407 | "SELECT mytable.myid, mytable.name, " |
| 1408 | "mytable.description FROM mytable WHERE " |
| 1409 | "EXISTS (SELECT 1 FROM myothertable WHERE " |
| 1410 | "myothertable.otherid = mytable.myid)", |
| 1411 | ) |
| 1412 | talias = table1.alias("ta") |
| 1413 | s = ( |
| 1414 | select(talias) |
| 1415 | .where(exists(1).where(table2.c.otherid == talias.c.myid)) |
| 1416 | .subquery("sq2") |
| 1417 | ) |
| 1418 | self.assert_compile( |
| 1419 | select(s, table1), |
| 1420 | "SELECT sq2.myid, sq2.name, " |
nothing calls this directly
no test coverage detected