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")])