def test_join_to_join_entities(self):
sess = create_session()
pa = with_polymorphic(Person, [Engineer])
pa_alias = with_polymorphic(Person, [Engineer], aliased=True)
eq_(
[
(p1.name, type(p1), p2.name, type(p2))
for (p1, p2) in sess.query(pa, pa_alias)
.join(
pa_alias,
or_(
pa.Engineer.primary_language
== pa_alias.Engineer.primary_language,
and_(
pa.Engineer.primary_language == None, # noqa
pa_alias.Engineer.primary_language == None,
pa.person_id > pa_alias.person_id,
),
),
)
.order_by(pa.name, pa_alias.name)
],
[
("dilbert", Engineer, "dilbert", Engineer),
("dogbert", Manager, "pointy haired boss", Boss),
("vlad", Engineer, "vlad", Engineer),
("wally", Engineer, "wally", Engineer),
],
)
def test_join_to_join_columns(self):
sess = create_session()
pa = with_polymorphic(Person, [Engineer])
pa_alias = with_polymorphic(Person, [Engineer], aliased=True)
eq_(
[
row
for row in sess.query(
pa.name,
pa.Engineer.primary_language,
pa_alias.name,
pa_alias.Engineer.primary_language,
)
.join(
pa_alias,
or_(
pa.Engineer.primary_language
== pa_alias.Engineer.primary_language,
and_(
pa.Engineer.primary_language == None, # noqa
pa_alias.Engineer.primary_language == None,
pa.person_id > pa_alias.person_id,
),
),
)
.order_by(pa.name, pa_alias.name)
],
[
("dilbert", "java", "dilbert", "java"),
("dogbert", None, "pointy haired boss", None),
("vlad", "cobol", "vlad", "cobol"),
("wally", "c++", "wally", "c++"),
],
)
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 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 test_any_wpoly(self):
DataContainer, Job, SubJob = (
self.classes.DataContainer,
self.classes.Job,
self.classes.SubJob,
)
Job_P = with_polymorphic(Job, SubJob, aliased=True, flat=True)
s = Session()
q = (s.query(Job).join(DataContainer.jobs).filter(
DataContainer.jobs.of_type(Job_P).any(Job_P.id < Job.id)))
self.assert_compile(
q,
"SELECT job.id AS job_id, job.type AS job_type, "
"job.widget_id AS job_widget_id, "
"job.container_id "
"AS job_container_id "
"FROM data_container "
"JOIN job ON data_container.id = job.container_id "
"WHERE EXISTS (SELECT 1 "
"FROM job AS job_1 LEFT OUTER JOIN subjob AS subjob_1 "
"ON job_1.id = subjob_1.id "
"WHERE data_container.id = job_1.container_id "
"AND job_1.id < job.id)",
)
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_with_polymorphic_any(self):
sess = Session()
wp = with_polymorphic(Person, [Engineer], aliased=True)
eq_(
sess.query(Company.company_id).filter(
Company.employees.of_type(wp).any(
wp.Engineer.primary_language == "java")).all(),
[(1, )],
)
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_with_poly_sub(self):
Company = _poly_fixtures.Company
Person = _poly_fixtures.Person
Engineer = _poly_fixtures.Engineer
emapper = inspect(Engineer)
cmapper = inspect(Company)
p_poly = with_polymorphic(Person, [Engineer])
e_poly_insp = inspect(p_poly.Engineer) # noqa - used by comment below
p_poly_insp = inspect(p_poly)
p1 = PathRegistry.coerce((p_poly_insp, emapper.attrs.machines))
# changes as of #5082: when a with_polymorphic is in the middle
# of a path, the natural path makes sure it uses the base mappers,
# however when it's at the root, the with_polymorphic stays in
# the natural path
# this behavior is the same as pre #5082, it was temporarily changed
# but this proved to be incorrect. The path starts on a
# with_polymorphic(), so a Query will "naturally" construct a path
# that comes from that wp.
eq_(p1.path, (e_poly_insp, emapper.attrs.machines))
eq_(p1.natural_path, (e_poly_insp, emapper.attrs.machines))
# this behavior is new as of the final version of #5082.
# the path starts on a normal entity and has a with_polymorphic
# in the middle, for this to match what Query will generate it needs
# to use the non aliased mappers in the natural path.
p2 = PathRegistry.coerce(
(
cmapper,
cmapper.attrs.employees,
p_poly_insp,
emapper.attrs.machines,
)
)
eq_(
p2.path,
(
cmapper,
cmapper.attrs.employees,
e_poly_insp,
emapper.attrs.machines,
),
)
eq_(
p2.natural_path,
(
cmapper,
cmapper.attrs.employees,
emapper,
emapper.attrs.machines,
),
)
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_threelevel_selectin_to_inline_options(self):
self._fixture_from_geometry(
{
"a": {
"subclasses": {
"b": {},
"c": {
"subclasses": {
"d": {"single": True},
"e": {"single": True},
}
},
}
}
}
)
a, b, c, d, e = self.classes("a", "b", "c", "d", "e")
sess = Session()
sess.add_all([d(d_data="d1"), e(e_data="e1")])
sess.commit()
c_alias = with_polymorphic(c, (d, e))
q = sess.query(a).options(selectin_polymorphic(a, [b, c_alias]))
result = self.assert_sql_execution(
testing.db,
q.all,
CompiledSQL(
"SELECT a.type AS a_type, a.id AS a_id, "
"a.a_data AS a_a_data FROM a",
{},
),
Or(
CompiledSQL(
"SELECT a.type AS a_type, c.id AS c_id, a.id AS a_id, "
"c.c_data AS c_c_data, c.e_data AS c_e_data, "
"c.d_data AS c_d_data "
"FROM a JOIN c ON a.id = c.id "
"WHERE a.id IN ([EXPANDING_primary_keys]) ORDER BY a.id",
[{"primary_keys": [1, 2]}],
),
CompiledSQL(
"SELECT a.type AS a_type, c.id AS c_id, a.id AS a_id, "
"c.c_data AS c_c_data, c.d_data AS c_d_data, "
"c.e_data AS c_e_data "
"FROM a JOIN c ON a.id = c.id "
"WHERE a.id IN ([EXPANDING_primary_keys]) ORDER BY a.id",
[{"primary_keys": [1, 2]}],
),
),
)
with self.assert_statement_count(testing.db, 0):
eq_(result, [d(d_data="d1"), e(e_data="e1")])
def go():
wp = with_polymorphic(Person, [Engineer, Manager],
aliased=True,
flat=True)
eq_(
sess.query(Company).join(
Company.employees.of_type(wp)).order_by(
Company.company_id, wp.person_id).options(
contains_eager(
Company.employees.of_type(wp))).all(),
[self.c1, self.c2],
)
def test_with_poly_use_mapper(self):
Person = _poly_fixtures.Person
Engineer = _poly_fixtures.Engineer
emapper = inspect(Engineer)
p_poly = with_polymorphic(Person, [Engineer], _use_mapper_path=True)
p_poly = inspect(p_poly)
p1 = PathRegistry.coerce((p_poly, emapper.attrs.machines))
# polymorphic AliasedClass with the "use_mapper_path" flag -
# the AliasedClass acts just like the base mapper
eq_(p1.path, (emapper, emapper.attrs.machines))
def test_join_base_to_sub(self):
sess = create_session()
pa = with_polymorphic(Person, [Engineer])
def go():
eq_(
sess.query(pa)
.filter(pa.Engineer.primary_language == "java")
.all(),
self._emps_wo_relationships_fixture()[0:1],
)
self.assert_sql_count(testing.db, go, 1)
def test_with_polymorphic_join_compile_one(self):
sess = Session()
self.assert_compile(
sess.query(Company).join(
Company.employees.of_type(
with_polymorphic(Person, [Engineer, Manager],
aliased=True,
flat=True))),
"SELECT companies.company_id AS companies_company_id, "
"companies.name AS companies_name FROM companies "
"JOIN %s" % (self._polymorphic_join_target([Engineer, Manager])),
)
def test_with_poly_base_one(self):
Person = _poly_fixtures.Person
Engineer = _poly_fixtures.Engineer
pmapper = inspect(Person)
emapper = inspect(Engineer)
p_poly = with_polymorphic(Person, [Engineer])
p_poly = inspect(p_poly)
# "name" is actually on Person, not Engineer
p1 = PathRegistry.coerce((p_poly, emapper.attrs.name))
# polymorphic AliasedClass - because "name" is on Person,
# we get Person, not Engineer
eq_(p1.path, (p_poly, pmapper.attrs.name))
def test_options_of_type(self):
with_poly = with_polymorphic(Person, [Engineer, Manager], flat=True)
for opt, serialized in [
(
sa.orm.joinedload(Company.employees.of_type(Engineer)),
[(Company, "employees", Engineer)],
),
(
sa.orm.joinedload(Company.employees.of_type(with_poly)),
[(Company, "employees", None)],
),
]:
opt2 = pickle.loads(pickle.dumps(opt))
eq_(opt.__getstate__()["path"], serialized)
eq_(opt2.__getstate__()["path"], serialized)
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_col_expression_base_plus_two_subs(self):
sess = create_session()
pa = with_polymorphic(Person, [Engineer, Manager])
eq_(
sess.query(
pa.name, pa.Engineer.primary_language, pa.Manager.manager_name
)
.filter(
or_(
pa.Engineer.primary_language == "java",
pa.Manager.manager_name == "dogbert",
)
)
.order_by(pa.Engineer.type)
.all(),
[("dilbert", "java", None), ("dogbert", None, "dogbert")],
)
def test_join_wpoly_innerjoin(self):
DataContainer, Job, SubJob = (
self.classes.DataContainer,
self.classes.Job,
self.classes.SubJob,
)
Job_P = with_polymorphic(Job, SubJob, innerjoin=True)
s = Session()
q = s.query(DataContainer).join(DataContainer.jobs.of_type(Job_P))
self.assert_compile(
q,
"SELECT data_container.id AS data_container_id, "
"data_container.name AS data_container_name "
"FROM data_container JOIN "
"(job JOIN subjob ON job.id = subjob.id) "
"ON data_container.id = job.container_id",
)
def test_with_polymorphic_join_exec_contains_eager_two(
self, contains_eager_option):
sess = Session()
wp = with_polymorphic(Person, [Engineer, Manager], aliased=True)
contains_eager_option = testing.resolve_lambda(contains_eager_option,
Company=Company,
wp=wp)
q = (sess.query(Company).join(Company.employees.of_type(wp)).order_by(
Company.company_id, wp.person_id).options(contains_eager_option))
def go():
eq_(q.all(), [self.c1, self.c2])
self.assert_sql_count(testing.db, go, 1)
self.assert_compile(
q,
self._test_with_polymorphic_join_exec_contains_eager_two_result(),
)
def test_join_explicit_wpoly_flat(self):
DataContainer, Job, SubJob = (
self.classes.DataContainer,
self.classes.Job,
self.classes.SubJob,
)
Job_P = with_polymorphic(Job, SubJob, flat=True)
s = Session()
q = s.query(DataContainer).join(Job_P, DataContainer.jobs)
self.assert_compile(
q,
"SELECT data_container.id AS data_container_id, "
"data_container.name AS data_container_name "
"FROM data_container JOIN "
"(job AS job_1 LEFT OUTER JOIN subjob AS subjob_1 "
"ON job_1.id = subjob_1.id) "
"ON data_container.id = job_1.container_id",
)
def test_with_poly_base_two(self):
Company = _poly_fixtures.Company
Person = _poly_fixtures.Person
Engineer = _poly_fixtures.Engineer
cmapper = inspect(Company)
pmapper = inspect(Person)
p_poly = with_polymorphic(Person, [Engineer])
e_poly_insp = inspect(p_poly.Engineer) # noqa - used by comment below
p_poly_insp = inspect(p_poly)
p1 = PathRegistry.coerce(
(
cmapper,
cmapper.attrs.employees,
p_poly_insp,
pmapper.attrs.paperwork,
)
)
eq_(
p1.path,
(
cmapper,
cmapper.attrs.employees,
p_poly_insp,
pmapper.attrs.paperwork,
),
)
eq_(
p1.natural_path,
(
cmapper,
cmapper.attrs.employees,
pmapper,
pmapper.attrs.paperwork,
),
)
def test_join_explicit_wpoly_full_alias(self):
DataContainer, Job, SubJob = (
self.classes.DataContainer,
self.classes.Job,
self.classes.SubJob,
)
Job_P = with_polymorphic(Job, SubJob, aliased=True)
s = Session()
q = s.query(DataContainer).join(Job_P, DataContainer.jobs)
self.assert_compile(
q,
"SELECT data_container.id AS data_container_id, "
"data_container.name AS data_container_name "
"FROM data_container JOIN "
"(SELECT job.id AS job_id, job.type AS job_type, "
"job.widget_id AS job_widget_id, "
"job.container_id AS job_container_id, "
"subjob.id AS subjob_id, subjob.attr AS subjob_attr "
"FROM job LEFT OUTER JOIN subjob ON job.id = subjob.id) "
"AS anon_1 ON data_container.id = anon_1.job_container_id",
)
def test_threelevel_selectin_to_inline_awkward_alias_options(self):
self._fixture_from_geometry(
{
"a": {
"subclasses": {
"b": {},
"c": {"subclasses": {"d": {}, "e": {}}},
}
}
}
)
a, b, c, d, e = self.classes("a", "b", "c", "d", "e")
sess = Session()
sess.add_all([d(d_data="d1"), e(e_data="e1")])
sess.commit()
from sqlalchemy_1_3 import select
a_table, c_table, d_table, e_table = self.tables("a", "c", "d", "e")
poly = (
select([a_table.c.id, a_table.c.type, c_table, d_table, e_table])
.select_from(
a_table.join(c_table).outerjoin(d_table).outerjoin(e_table)
)
.apply_labels()
.alias("poly")
)
c_alias = with_polymorphic(c, (d, e), poly)
q = (
sess.query(a)
.options(selectin_polymorphic(a, [b, c_alias]))
.order_by(a.id)
)
result = self.assert_sql_execution(
testing.db,
q.all,
CompiledSQL(
"SELECT a.type AS a_type, a.id AS a_id, "
"a.a_data AS a_a_data FROM a ORDER BY a.id",
{},
),
Or(
# here, the test is that the adaptation of "a" takes place
CompiledSQL(
"SELECT poly.a_type AS poly_a_type, "
"poly.c_id AS poly_c_id, "
"poly.a_id AS poly_a_id, poly.c_c_data AS poly_c_c_data, "
"poly.e_id AS poly_e_id, poly.e_e_data AS poly_e_e_data, "
"poly.d_id AS poly_d_id, poly.d_d_data AS poly_d_d_data "
"FROM (SELECT a.id AS a_id, a.type AS a_type, "
"c.id AS c_id, "
"c.c_data AS c_c_data, d.id AS d_id, "
"d.d_data AS d_d_data, "
"e.id AS e_id, e.e_data AS e_e_data FROM a JOIN c "
"ON a.id = c.id LEFT OUTER JOIN d ON c.id = d.id "
"LEFT OUTER JOIN e ON c.id = e.id) AS poly "
"WHERE poly.a_id IN ([EXPANDING_primary_keys]) "
"ORDER BY poly.a_id",
[{"primary_keys": [1, 2]}],
),
CompiledSQL(
"SELECT poly.a_type AS poly_a_type, "
"poly.c_id AS poly_c_id, "
"poly.a_id AS poly_a_id, poly.c_c_data AS poly_c_c_data, "
"poly.d_id AS poly_d_id, poly.d_d_data AS poly_d_d_data, "
"poly.e_id AS poly_e_id, poly.e_e_data AS poly_e_e_data "
"FROM (SELECT a.id AS a_id, a.type AS a_type, "
"c.id AS c_id, c.c_data AS c_c_data, d.id AS d_id, "
"d.d_data AS d_d_data, e.id AS e_id, "
"e.e_data AS e_e_data FROM a JOIN c ON a.id = c.id "
"LEFT OUTER JOIN d ON c.id = d.id "
"LEFT OUTER JOIN e ON c.id = e.id) AS poly "
"WHERE poly.a_id IN ([EXPANDING_primary_keys]) "
"ORDER BY poly.a_id",
[{"primary_keys": [1, 2]}],
),
),
)
with self.assert_statement_count(testing.db, 0):
eq_(result, [d(d_data="d1"), e(e_data="e1")])
