def go():
target = with_polymorphic(Person, Engineer)
eq_(
sess.query(Company).filter_by(company_id=1).options(
subqueryload(Company.employees.of_type(target))).all(),
[self._company_with_emps_fixture()[0]],
)
def test_subqueryload_post_context_w_cancelling_event(
self, before_compile_nobake_fixture):
User = self.classes.User
Address = self.classes.Address
assert_result = [
User(id=7,
addresses=[Address(id=1, email_address="*****@*****.**")])
]
self.bakery = baked.bakery(size=3)
bq = self.bakery(lambda s: s.query(User))
bq += lambda q: q.options(subqueryload(User.addresses))
bq += lambda q: q.order_by(User.id)
bq += lambda q: q.filter(User.name == bindparam("name"))
sess = Session()
def set_params(q):
return q.params(name="jack")
# test that the changes we make using with_post_criteria()
# are also applied to the subqueryload query.
def go():
result = bq(sess).with_post_criteria(set_params).all()
eq_(assert_result, result)
self.assert_sql_count(testing.db, go, 2)
def test_load(self):
s = Session()
with_poly = with_polymorphic(Person, [Engineer, Manager], flat=True)
emp = (
s.query(Company)
.options(subqueryload(Company.employees.of_type(with_poly)))
.first()
)
pickle.loads(pickle.dumps(emp))
def test_twolevel_subqueryload_wsubclass_mapper_term(self):
DataContainer, SubJob = self.classes.DataContainer, self.classes.SubJob
s = Session(testing.db)
sj_alias = aliased(SubJob)
q = s.query(DataContainer).options(
subqueryload(DataContainer.jobs.of_type(sj_alias)).subqueryload(
sj_alias.widget))
def go():
eq_(q.all(), self._dc_fixture())
self.assert_sql_count(testing.db, go, 3)
def test_subquery_wsubclass(self):
DataContainer, SubJob = (
self.classes.DataContainer,
self.classes.SubJob,
)
s = Session(testing.db)
q = s.query(DataContainer).options(
subqueryload(DataContainer.jobs.of_type(SubJob)))
def go():
eq_(q.all(), self._dc_fixture())
self.assert_sql_count(testing.db, go, 6)
def test_all_subq_query(self):
A, B, B2, C, C2, D = self.classes("A", "B", "B2", "C", "C2", "D")
session = Session(testing.db)
b_b2 = with_polymorphic(B, [B2], flat=True)
c_c2 = with_polymorphic(C, [C2], flat=True)
q = session.query(A).options(
subqueryload(A.bs.of_type(b_b2)).subqueryload(
b_b2.cs.of_type(c_c2)).subqueryload(c_c2.ds))
self.assert_sql_execution(
testing.db,
q.all,
CompiledSQL("SELECT t_a.id AS t_a_id FROM t_a", {}),
CompiledSQL(
"SELECT t_b_1.type AS t_b_1_type, t_b_1.id AS t_b_1_id, "
"t_b_1.a_id AS t_b_1_a_id, t_b2_1.id AS t_b2_1_id, "
"anon_1.t_a_id AS anon_1_t_a_id FROM "
"(SELECT t_a.id AS t_a_id FROM t_a) AS anon_1 "
"JOIN (t_b AS t_b_1 LEFT OUTER JOIN t_b2 AS t_b2_1 "
"ON t_b_1.id = t_b2_1.id) ON anon_1.t_a_id = t_b_1.a_id",
{},
),
CompiledSQL(
"SELECT t_c_1.type AS t_c_1_type, t_c_1.id AS t_c_1_id, "
"t_c_1.b_id AS t_c_1_b_id, t_c2_1.id AS t_c2_1_id, "
"t_b_1.id AS t_b_1_id FROM (SELECT t_a.id AS t_a_id FROM t_a) "
"AS anon_1 JOIN (t_b AS t_b_1 LEFT OUTER JOIN t_b2 AS t_b2_1 "
"ON t_b_1.id = t_b2_1.id) ON anon_1.t_a_id = t_b_1.a_id "
"JOIN (t_c AS t_c_1 LEFT OUTER JOIN t_c2 AS t_c2_1 ON "
"t_c_1.id = t_c2_1.id) ON t_b_1.id = t_c_1.b_id",
{},
),
CompiledSQL(
"SELECT t_d.id AS t_d_id, t_d.c_id AS t_d_c_id, "
"t_c_1.id AS t_c_1_id "
"FROM (SELECT t_a.id AS t_a_id FROM t_a) AS anon_1 "
"JOIN (t_b AS t_b_1 LEFT OUTER JOIN t_b2 AS t_b2_1 "
"ON t_b_1.id = t_b2_1.id) "
"ON anon_1.t_a_id = t_b_1.a_id "
"JOIN (t_c AS t_c_1 LEFT OUTER JOIN t_c2 AS t_c2_1 "
"ON t_c_1.id = t_c2_1.id) "
"ON t_b_1.id = t_c_1.b_id "
"JOIN t_d ON t_c_1.id = t_d.c_id",
{},
),
)
def test_twolevel_subqueryload_wsubclass(self):
ParentThing, DataContainer, SubJob = (
self.classes.ParentThing,
self.classes.DataContainer,
self.classes.SubJob,
)
s = Session(testing.db)
q = s.query(ParentThing).options(
subqueryload(ParentThing.container).subqueryload(
DataContainer.jobs.of_type(SubJob)))
def go():
eq_(q.all(), self._fixture())
self.assert_sql_count(testing.db, go, 7)
def go():
eq_(
sess.query(Company).filter_by(company_id=1).options(
subqueryload(Company.employees.of_type(Engineer))).all(),
[self._company_with_emps_fixture()[0]],
)
def test_subquery_eagerloading(self):
User = self.classes.User
Address = self.classes.Address
Order = self.classes.Order
# Override the default bakery for one with a smaller size. This used to
# trigger a bug when unbaking subqueries.
self.bakery = baked.bakery(size=3)
base_bq = self.bakery(lambda s: s.query(User))
base_bq += lambda q: q.options(subqueryload(User.addresses),
subqueryload(User.orders))
base_bq += lambda q: q.order_by(User.id)
assert_result = [
User(
id=7,
addresses=[Address(id=1, email_address="*****@*****.**")],
orders=[Order(id=1), Order(id=3),
Order(id=5)],
),
User(
id=8,
addresses=[
Address(id=2, email_address="*****@*****.**"),
Address(id=3, email_address="*****@*****.**"),
Address(id=4, email_address="*****@*****.**"),
],
),
User(
id=9,
addresses=[Address(id=5)],
orders=[Order(id=2), Order(id=4)],
),
User(id=10, addresses=[]),
]
for i in range(4):
for cond1, cond2 in itertools.product(*[(False, True)
for j in range(2)]):
bq = base_bq._clone()
sess = Session()
if cond1:
bq += lambda q: q.filter(User.name == "jack")
else:
bq += lambda q: q.filter(User.name.like("%ed%"))
if cond2:
ct = func.count(Address.id).label("count")
subq = (sess.query(ct, Address.user_id).group_by(
Address.user_id).having(ct > 2).subquery())
bq += lambda q: q.join(subq)
if cond2:
if cond1:
def go():
result = bq(sess).all()
eq_([], result)
self.assert_sql_count(testing.db, go, 1)
else:
def go():
result = bq(sess).all()
eq_(assert_result[1:2], result)
self.assert_sql_count(testing.db, go, 3)
else:
if cond1:
def go():
result = bq(sess).all()
eq_(assert_result[0:1], result)
self.assert_sql_count(testing.db, go, 3)
else:
def go():
result = bq(sess).all()
eq_(assert_result[1:3], result)
self.assert_sql_count(testing.db, go, 3)
sess.close()
def go():
sess = Session()
sess.query(B).options(subqueryload(B.as_.of_type(ASub))).all()
sess.close()