def go():
eq_(
sess.query(Company).filter_by(company_id=1).options(
joinedload(Company.employees.of_type(Manager)),
joinedload(Company.employees.of_type(Engineer)),
).all(),
[self._company_with_emps_fixture()[0]],
)
def go():
for i in range(100):
q = sess.query(A).options(
joinedload(A.bs).joinedload(B.cs).joinedload(C.ds),
joinedload(A.es).joinedload(E.fs),
defaultload(A.es).joinedload(E.gs),
)
q._compile_context()
def go():
target = with_polymorphic(Person, Engineer, flat=True)
eq_(
sess.query(Company).filter_by(company_id=1).options(
joinedload(Company.employees.of_type(target))).all(),
[self._company_with_emps_fixture()[0]],
)
def go():
c2 = (session.query(Company).options(joinedload(
Company.employees)).get(c.id))
assert set([repr(x) for x in c2.employees]) == set([
"Engineer Kurt knows how to hack",
"Manager Tom knows how to manage things",
])
def test_joinedload_explicit_with_flataliased_poly_compile(self):
sess = Session()
target = with_polymorphic(Person, Engineer, flat=True)
q = (sess.query(Company).filter_by(company_id=1).options(
joinedload(Company.employees.of_type(target))))
self.assert_compile(
q,
"SELECT companies.company_id AS companies_company_id, "
"companies.name AS companies_name, "
"people_1.person_id AS people_1_person_id, "
"people_1.company_id AS people_1_company_id, "
"people_1.name AS people_1_name, people_1.type AS people_1_type, "
"engineers_1.person_id AS engineers_1_person_id, "
"engineers_1.status AS engineers_1_status, "
"engineers_1.engineer_name AS engineers_1_engineer_name, "
"engineers_1.primary_language AS engineers_1_primary_language "
"FROM companies LEFT OUTER JOIN (people AS people_1 "
"LEFT OUTER JOIN engineers AS engineers_1 "
"ON people_1.person_id = engineers_1.person_id "
"LEFT OUTER JOIN managers AS managers_1 "
"ON people_1.person_id = managers_1.person_id) "
"ON companies.company_id = people_1.company_id "
"WHERE companies.company_id = :company_id_1 "
"ORDER BY people_1.person_id",
)
def test_joined_lock_no_subquery(self):
A = self.classes.A
with self.run_test() as s:
s.query(A).options(joinedload(A.bs)).with_for_update().all()
# no subquery, should be locked
self._assert_a_is_locked(True)
self._assert_b_is_locked(True)
def test_inner_joinedload_wo_limit(self):
User = self.classes.User
sess = Session()
sess.query(User).options(
joinedload(User.addresses, innerjoin=True)
).with_for_update().all()
sess.close()
def test_joined_lock_subquery(self):
A = self.classes.A
with self.run_test() as s:
s.query(A).options(joinedload(A.bs)).with_for_update().first()
# test for issue #4246, should be locked
self._assert_a_is_locked(True)
self._assert_b_is_locked(True)
def test_joinedload_explicit_with_unaliased_poly_compile(self):
sess = Session()
target = with_polymorphic(Person, Engineer)
q = (sess.query(Company).filter_by(company_id=1).options(
joinedload(Company.employees.of_type(target))))
assert_raises_message(
sa_exc.InvalidRequestError,
"Detected unaliased columns when generating joined load.",
q._compile_context,
)
def test_joinedload_wsubclass(self):
DataContainer, SubJob = (
self.classes.DataContainer,
self.classes.SubJob,
)
s = Session(testing.db)
q = s.query(DataContainer).options(
joinedload(DataContainer.jobs.of_type(SubJob)))
def go():
eq_(q.all(), self._dc_fixture())
self.assert_sql_count(testing.db, go, 5)
def go():
eq_(
[
Dest(many_a=[
A(aname="a1"),
B(bname="b1"),
B(bname="b2"),
C(cname="c1"),
]),
Dest(many_a=[A(aname="a2"), C(cname="c2")]),
],
sess.query(Dest).options(joinedload(Dest.many_a)).order_by(
Dest.id).all(),
)
def test_outer_joinedload_wo_limit(self):
User = self.classes.User
sess = Session()
q = sess.query(User).options(
joinedload(User.addresses, innerjoin=False)
)
if testing.against("postgresql"):
q = q.with_for_update(of=User)
else:
q = q.with_for_update()
q.all()
sess.close()
def test_fetch_results(self):
A, B, C, D, E, F, G = self.classes("A", "B", "C", "D", "E", "F", "G")
sess = Session()
q = sess.query(A).options(
joinedload(A.bs).joinedload(B.cs).joinedload(C.ds),
joinedload(A.es).joinedload(E.fs),
defaultload(A.es).joinedload(E.gs),
)
context = q._compile_context()
attributes = dict(context.attributes)
@profiling.function_call_count()
def go():
for i in range(100):
# make sure these get reset each time
context.attributes = attributes.copy()
obj = q._execute_and_instances(context)
list(obj)
sess.close()
go()
def test_update_with_explicit_joinedload(self):
User = self.classes.User
sess = Session()
john, jack, jill, jane = sess.query(User).order_by(User.id).all()
sess.query(User).options(joinedload(User.documents)).filter(
User.age > 29).update({"age": User.age - 10},
synchronize_session="fetch")
eq_([john.age, jack.age, jill.age, jane.age], [25, 37, 29, 27])
eq_(
sess.query(User.age).order_by(User.id).all(),
list(zip([25, 37, 29, 27])),
)
def test_inner_joinedload_w_limit(self):
User = self.classes.User
sess = Session()
q = (
sess.query(User)
.options(joinedload(User.addresses, innerjoin=True))
.with_for_update()
.limit(1)
)
if testing.against("oracle"):
assert_raises_message(exc.DatabaseError, "ORA-02014", q.all)
else:
q.all()
sess.close()
def test_query_one(self):
q = (
Session.query(User)
.filter(User.name == "ed")
.options(joinedload(User.addresses))
)
q2 = serializer.loads(serializer.dumps(q, -1), users.metadata, Session)
def go():
eq_(
q2.all(),
[
User(
name="ed",
addresses=[
Address(id=2),
Address(id=3),
Address(id=4),
],
)
],
)
self.assert_sql_count(testing.db, go, 1)
eq_(
q2.join(User.addresses)
.filter(Address.email == "*****@*****.**")
.value(func.count(literal_column("*"))),
1,
)
u1 = Session.query(User).get(8)
q = (
Session.query(Address)
.filter(Address.user == u1)
.order_by(desc(Address.email))
)
q2 = serializer.loads(serializer.dumps(q, -1), users.metadata, Session)
eq_(
q2.all(),
[
Address(email="*****@*****.**"),
Address(email="*****@*****.**"),
Address(email="*****@*****.**"),
],
)
def test_joinedload_wpoly(self):
DataContainer, Job, SubJob = (
self.classes.DataContainer,
self.classes.Job,
self.classes.SubJob,
)
Job_P = with_polymorphic(Job, SubJob, aliased=True)
s = Session(testing.db)
q = s.query(DataContainer).options(
joinedload(DataContainer.jobs.of_type(Job_P)))
def go():
eq_(q.all(), self._dc_fixture())
self.assert_sql_count(testing.db, go, 5)
def test_for_update_on_inner_w_joinedload(self):
User = self.classes.User
sess = Session()
self.assert_compile(
sess.query(User)
.options(joinedload(User.addresses))
.with_for_update()
.limit(1),
"SELECT anon_1.users_id AS anon_1_users_id, anon_1.users_name "
"AS anon_1_users_name, addresses_1.id AS addresses_1_id, "
"addresses_1.user_id AS addresses_1_user_id, "
"addresses_1.email_address AS addresses_1_email_address "
"FROM (SELECT users.id AS users_id, users.name AS users_name "
"FROM users LIMIT %s FOR UPDATE) AS anon_1 "
"LEFT OUTER JOIN addresses AS addresses_1 "
"ON anon_1.users_id = addresses_1.user_id FOR UPDATE",
dialect="mysql",
)
def test_query_opts_unbound_branching(self):
A, B, C, D, E, F, G = self.classes("A", "B", "C", "D", "E", "F", "G")
base = joinedload(A.bs)
opts = [
base.joinedload(B.cs),
base.joinedload(B.ds),
base.joinedload(B.es),
base.joinedload(B.fs),
]
q = Session().query(A)
@profiling.function_call_count(warmup=1)
def go():
q.options(*opts)
go()
def test_generate_cache_key_unbound_branching(self):
A, B, C, D, E, F, G = self.classes("A", "B", "C", "D", "E", "F", "G")
base = joinedload(A.bs)
opts = [
base.joinedload(B.cs),
base.joinedload(B.ds),
base.joinedload(B.es),
base.joinedload(B.fs),
]
cache_path = inspect(A)._path_registry
@profiling.function_call_count(warmup=1)
def go():
for opt in opts:
opt._generate_cache_key(cache_path)
go()
def test_for_update_on_inner_w_joinedload_no_render_oracle(self):
User = self.classes.User
sess = Session()
self.assert_compile(
sess.query(User)
.options(joinedload(User.addresses))
.with_for_update()
.limit(1),
"SELECT anon_1.users_id AS anon_1_users_id, "
"anon_1.users_name AS anon_1_users_name, "
"addresses_1.id AS addresses_1_id, "
"addresses_1.user_id AS addresses_1_user_id, "
"addresses_1.email_address AS addresses_1_email_addres_1 "
"FROM (SELECT users_id, users_name FROM "
"(SELECT users.id AS users_id, users.name AS users_name "
"FROM users) WHERE ROWNUM <= :param_1) anon_1 "
"LEFT OUTER JOIN addresses addresses_1 "
"ON anon_1.users_id = addresses_1.user_id FOR UPDATE",
dialect="oracle",
)
def test_joinedload(self):
A, B = self.classes("A", "B")
sess = Session()
with self.sql_execution_asserter() as asserter:
# note this is many-to-one. use_get is unconditionally turned
# off for relationship to aliased class for now.
a1 = sess.query(A).options(joinedload(A.b)).first()
eq_(a1.b, B(id=1))
asserter.assert_(
CompiledSQL(
"SELECT a.id AS a_id, a.b_id AS a_b_id, b_1.id AS b_1_id "
"FROM a LEFT OUTER JOIN (b AS b_1 "
"JOIN d AS d_1 ON d_1.b_id = b_1.id "
"JOIN c AS c_1 ON c_1.id = d_1.c_id) ON a.b_id = b_1.id "
"LIMIT :param_1",
[{
"param_1": 1
}],
))
def no_opt():
q = session.query(Parent).options(
joinedload(Parent.children.of_type(ChildSubclass1))
)
return self.assert_sql_execution(
testing.db,
q.all,
CompiledSQL(
"SELECT parent.id AS parent_id, "
"anon_1.child_id AS anon_1_child_id, "
"anon_1.child_parent_id AS anon_1_child_parent_id, "
"anon_1.child_type AS anon_1_child_type, "
"anon_1.child_subclass1_id AS anon_1_child_subclass1_id "
"FROM parent "
"LEFT OUTER JOIN (SELECT child.id AS child_id, "
"child.parent_id AS child_parent_id, "
"child.type AS child_type, "
"child_subclass1.id AS child_subclass1_id "
"FROM child "
"LEFT OUTER JOIN child_subclass1 "
"ON child.id = child_subclass1.id) AS anon_1 "
"ON parent.id = anon_1.child_parent_id",
{},
),
CompiledSQL(
"SELECT child_subclass1.id AS child_subclass1_id, "
"child.id AS child_id, "
"child.parent_id AS child_parent_id, "
"child.type AS child_type "
"FROM child JOIN child_subclass1 "
"ON child.id = child_subclass1.id "
"WHERE child.id IN ([EXPANDING_primary_keys]) "
"ORDER BY child.id",
[{"primary_keys": [1]}],
),
)
def generate():
objects = s.query(User).options(joinedload(User.addresses)).all()
gc_collect()
return objects
def _test_options_dont_pollute(self, enable_baked):
Parent, ChildSubclass1, Other = self.classes(
"Parent", "ChildSubclass1", "Other"
)
session = Session(enable_baked_queries=enable_baked)
def no_opt():
q = session.query(Parent).options(
joinedload(Parent.children.of_type(ChildSubclass1))
)
return self.assert_sql_execution(
testing.db,
q.all,
CompiledSQL(
"SELECT parent.id AS parent_id, "
"anon_1.child_id AS anon_1_child_id, "
"anon_1.child_parent_id AS anon_1_child_parent_id, "
"anon_1.child_type AS anon_1_child_type, "
"anon_1.child_subclass1_id AS anon_1_child_subclass1_id "
"FROM parent "
"LEFT OUTER JOIN (SELECT child.id AS child_id, "
"child.parent_id AS child_parent_id, "
"child.type AS child_type, "
"child_subclass1.id AS child_subclass1_id "
"FROM child "
"LEFT OUTER JOIN child_subclass1 "
"ON child.id = child_subclass1.id) AS anon_1 "
"ON parent.id = anon_1.child_parent_id",
{},
),
CompiledSQL(
"SELECT child_subclass1.id AS child_subclass1_id, "
"child.id AS child_id, "
"child.parent_id AS child_parent_id, "
"child.type AS child_type "
"FROM child JOIN child_subclass1 "
"ON child.id = child_subclass1.id "
"WHERE child.id IN ([EXPANDING_primary_keys]) "
"ORDER BY child.id",
[{"primary_keys": [1]}],
),
)
result = no_opt()
with self.assert_statement_count(testing.db, 1):
eq_(result, [Parent(children=[ChildSubclass1(others=[Other()])])])
session.expunge_all()
q = session.query(Parent).options(
joinedload(Parent.children.of_type(ChildSubclass1)).joinedload(
ChildSubclass1.others
)
)
result = self.assert_sql_execution(
testing.db,
q.all,
CompiledSQL(
"SELECT parent.id AS parent_id, "
"anon_1.child_id AS anon_1_child_id, "
"anon_1.child_parent_id AS anon_1_child_parent_id, "
"anon_1.child_type AS anon_1_child_type, "
"anon_1.child_subclass1_id AS anon_1_child_subclass1_id, "
"other_1.id AS other_1_id, "
"other_1.child_subclass_id AS other_1_child_subclass_id "
"FROM parent LEFT OUTER JOIN "
"(SELECT child.id AS child_id, "
"child.parent_id AS child_parent_id, "
"child.type AS child_type, "
"child_subclass1.id AS child_subclass1_id "
"FROM child LEFT OUTER JOIN child_subclass1 "
"ON child.id = child_subclass1.id) AS anon_1 "
"ON parent.id = anon_1.child_parent_id "
"LEFT OUTER JOIN other AS other_1 "
"ON anon_1.child_subclass1_id = other_1.child_subclass_id",
{},
),
CompiledSQL(
"SELECT child_subclass1.id AS child_subclass1_id, "
"child.id AS child_id, child.parent_id AS child_parent_id, "
"child.type AS child_type, other_1.id AS other_1_id, "
"other_1.child_subclass_id AS other_1_child_subclass_id "
"FROM child JOIN child_subclass1 "
"ON child.id = child_subclass1.id "
"LEFT OUTER JOIN other AS other_1 "
"ON child_subclass1.id = other_1.child_subclass_id "
"WHERE child.id IN ([EXPANDING_primary_keys]) "
"ORDER BY child.id",
[{"primary_keys": [1]}],
),
)
with self.assert_statement_count(testing.db, 0):
eq_(result, [Parent(children=[ChildSubclass1(others=[Other()])])])
session.expunge_all()
result = no_opt()
with self.assert_statement_count(testing.db, 1):
eq_(result, [Parent(children=[ChildSubclass1(others=[Other()])])])
def generate():
s.query(User).options(joinedload(User.addresses)).all()
def go():
joinedload(User.addresses)
