Python sqlalchemy.orm.join() Examples

def test_two_level(self):
        Company, Person, Paperwork = self.classes(
            "Company", "Person", "Paperwork"
        )

        stmt1 = select(Company).select_from(
            orm_join(Company, Person, Company.employees).join(
                Paperwork, Person.paperwork
            )
        )

        stmt2 = select(Company).join(Company.employees).join(Person.paperwork)
        stmt3 = (
            Session()
            .query(Company)
            .join(Company.employees)
            .join(Person.paperwork)
            ._final_statement(legacy_query_style=False)
        )

        self.assert_compile(stmt1, self.person_paperwork_expected)
        self.assert_compile(stmt2, self.person_paperwork_expected)
        self.assert_compile(stmt3, self.person_paperwork_expected) 

def _splice_into_center(self, other):
        """Splice a join into the center.

        Given join(a, b) and join(b, c), return join(a, b).join(c)

        """
        leftmost = other
        while isinstance(leftmost, sql.Join):
            leftmost = leftmost.left

        assert self.right is leftmost

        left = _ORMJoin(
            self.left, other.left,
            self.onclause, isouter=self.isouter,
            _left_memo=self._left_memo,
            _right_memo=other._left_memo
        )

        return _ORMJoin(
            left,
            other.right,
            other.onclause, isouter=other.isouter,
            _right_memo=other._right_memo
        ) 

def test_straight_whereclause(self):
        Company, Person, Manager, Engineer = self.classes(
            "Company", "Person", "Manager", "Engineer"
        )

        stmt1 = (
            select(Company)
            .select_from(orm_join(Company, Person, Company.employees))
            .where(Person.name == "ed")
        )

        stmt2 = (
            select(Company).join(Company.employees).where(Person.name == "ed")
        )
        stmt3 = (
            Session()
            .query(Company)
            .join(Company.employees)
            .filter(Person.name == "ed")
            ._final_statement(legacy_query_style=False)
        )

        self.assert_compile(stmt1, self.c_to_p_whereclause)
        self.assert_compile(stmt2, self.c_to_p_whereclause)
        self.assert_compile(stmt3, self.c_to_p_whereclause) 

def test_straight(self):
        Company, Person, Manager, Engineer = self.classes(
            "Company", "Person", "Manager", "Engineer"
        )

        stmt1 = select(Company).select_from(
            orm_join(Company, Person, Company.employees)
        )
        stmt2 = select(Company).join(Company.employees)
        stmt3 = (
            Session()
            .query(Company)
            .join(Company.employees)
            ._final_statement(legacy_query_style=False)
        )

        self.assert_compile(stmt1, self.straight_company_to_person_expected)
        self.assert_compile(stmt2, self.straight_company_to_person_expected)
        self.assert_compile(stmt3, self.straight_company_to_person_expected) 

def _splice_into_center(self, other):
        """Splice a join into the center.

        Given join(a, b) and join(b, c), return join(a, b).join(c)

        """
        leftmost = other
        while isinstance(leftmost, sql.Join):
            leftmost = leftmost.left

        assert self.right is leftmost

        left = _ORMJoin(
            self.left, other.left,
            self.onclause, isouter=self.isouter,
            _left_memo=self._left_memo,
            _right_memo=other._left_memo
        )

        return _ORMJoin(
            left,
            other.right,
            other.onclause, isouter=other.isouter,
            _right_memo=other._right_memo
        ) 

def test_join_from_polymorphic_flag_aliased_one_future(self):
        sess = create_session()

        pa = aliased(Paperwork)
        eq_(
            sess.execute(
                future_select(Person)
                .order_by(Person.person_id)
                .join(Person.paperwork.of_type(pa))
                .filter(pa.description.like("%review%"))
            )
            .unique()
            .scalars()
            .all(),
            [b1, m1],
        ) 

def _splice_into_center(self, other):
        """Splice a join into the center.

        Given join(a, b) and join(b, c), return join(a, b).join(c)

        """
        leftmost = other
        while isinstance(leftmost, sql.Join):
            leftmost = leftmost.left

        assert self.right is leftmost

        left = _ORMJoin(
            self.left, other.left,
            self.onclause, isouter=self.isouter,
            _left_memo=self._left_memo,
            _right_memo=other._left_memo
        )

        return _ORMJoin(
            left,
            other.right,
            other.onclause, isouter=other.isouter,
            _right_memo=other._right_memo
        ) 

def test_join_from_columns_or_subclass_four(self):
        sess = create_session()
        # Load Person.name, joining from Person -> paperwork, get all
        # the people.
        expected = [
            ("dilbert",),
            ("dilbert",),
            ("dogbert",),
            ("dogbert",),
            ("pointy haired boss",),
            ("vlad",),
            ("wally",),
            ("wally",),
        ]
        eq_(
            sess.query(Person.name)
            .join(paperwork, Person.person_id == paperwork.c.person_id)
            .order_by(Person.name)
            .all(),
            expected,
        ) 

def test_join_from_columns_or_subclass_three(self):
        sess = create_session()
        expected = [
            ("dilbert",),
            ("dilbert",),
            ("dogbert",),
            ("dogbert",),
            ("pointy haired boss",),
            ("vlad",),
            ("wally",),
            ("wally",),
        ]
        eq_(
            sess.query(Person.name)
            .join(Paperwork, Person.paperwork)
            .order_by(Person.name)
            .all(),
            expected,
        ) 

def _splice_into_center(self, other):
        """Splice a join into the center.

        Given join(a, b) and join(b, c), return join(a, b).join(c)

        """
        leftmost = other
        while isinstance(leftmost, sql.Join):
            leftmost = leftmost.left

        assert self.right is leftmost

        left = _ORMJoin(
            self.left, other.left,
            self.onclause, isouter=self.isouter,
            _left_memo=self._left_memo,
            _right_memo=other._left_memo
        )

        return _ORMJoin(
            left,
            other.right,
            other.onclause, isouter=other.isouter,
            _right_memo=other._right_memo
        ) 

def test_join_from_with_polymorphic_nonaliased_one_future(self):
        sess = create_session()

        pm = with_polymorphic(Person, [Manager])
        eq_(
            sess.execute(
                future_select(pm)
                .order_by(pm.person_id)
                .join(pm.paperwork)
                .filter(Paperwork.description.like("%review%"))
            )
            .unique()
            .scalars()
            .all(),
            [b1, m1],
        ) 

def test_join_from_polymorphic_explicit_aliased_three(self):
        sess = create_session()
        pa = aliased(Paperwork)
        eq_(
            sess.query(Engineer)
            .order_by(Person.person_id)
            .join(pa, "paperwork")
            .filter(pa.description.like("%#2%"))
            .all(),
            [e1],
        ) 

def test_join_from_columns_or_subclass_two(self):
        sess = create_session()
        expected = [("dogbert",), ("dogbert",), ("pointy haired boss",)]
        eq_(
            sess.query(Manager.name)
            .join(Paperwork, Manager.paperwork)
            .order_by(Manager.name)
            .all(),
            expected,
        ) 

def test_join_from_polymorphic_aliased_four(self):
        sess = create_session()
        pa = aliased(Paperwork)
        eq_(
            sess.query(Person)
            .order_by(Person.person_id)
            .join(pa, "paperwork")
            .filter(Person.name.like("%dog%"))
            .filter(pa.description.like("%#2%"))
            .all(),
            [m1],
        ) 

def test_join_from_with_polymorphic_nonaliased_one(self):
        sess = create_session()
        eq_(
            sess.query(Person)
            .with_polymorphic(Manager)
            .order_by(Person.person_id)
            .join("paperwork")
            .filter(Paperwork.description.like("%review%"))
            .all(),
            [b1, m1],
        ) 

def test_join_from_columns_or_subclass_five(self):
        sess = create_session()
        # same, on manager.  get only managers.
        expected = [("dogbert",), ("dogbert",), ("pointy haired boss",)]
        eq_(
            sess.query(Manager.name)
            .join(paperwork, Manager.person_id == paperwork.c.person_id)
            .order_by(Person.name)
            .all(),
            expected,
        ) 

def test_join_from_columns_or_subclass_nine(self):
        sess = create_session()
        eq_(
            sess.query(Manager.person_id)
            .join(paperwork, Manager.person_id == paperwork.c.person_id)
            .order_by(Manager.name)
            .all(),
            [(4,), (4,), (3,)],
        ) 

def test_join_from_columns_or_subclass_eight(self):
        sess = create_session()
        expected = [("dogbert",), ("dogbert",), ("pointy haired boss",)]
        eq_(
            sess.query(Manager.name)
            .join(paperwork, Manager.person_id == paperwork.c.person_id)
            .order_by(Manager.name)
            .all(),
            expected,
        ) 

def test_join_from_columns_or_subclass_ten(self):
        sess = create_session()
        expected = [
            ("pointy haired boss", "review #1"),
            ("dogbert", "review #2"),
            ("dogbert", "review #3"),
        ]
        eq_(
            sess.query(Manager.name, Paperwork.description)
            .join(Paperwork, Manager.person_id == Paperwork.person_id)
            .order_by(Paperwork.paperwork_id)
            .all(),
            expected,
        ) 

def test_wpoly_aliased_of_type(self):
        Company, Person, Manager, Engineer = self.classes(
            "Company", "Person", "Manager", "Engineer"
        )
        s = Session()

        p1 = with_polymorphic(Person, "*", aliased=True)

        stmt1 = select(Company).select_from(
            orm_join(Company, p1, Company.employees.of_type(p1))
        )

        stmt2 = select(Company).join(p1, Company.employees.of_type(p1))

        stmt3 = (
            s.query(Company)
            .join(Company.employees.of_type(p1))
            ._final_statement(legacy_query_style=False)
        )

        expected = (
            "SELECT companies.company_id, companies.name FROM companies "
            "JOIN %s" % self.aliased_pjoin
        )

        self.assert_compile(stmt1, expected)
        self.assert_compile(stmt2, expected)
        self.assert_compile(stmt3, expected) 

def test_join_from_polymorphic_flag_aliased_three(self):
        sess = create_session()
        eq_(
            sess.query(Engineer)
            .order_by(Person.person_id)
            .join("paperwork", aliased=True)
            .filter(Paperwork.description.like("%#2%"))
            .all(),
            [e1],
        ) 

def test_join_from_polymorphic_flag_aliased_two(self):
        sess = create_session()
        eq_(
            sess.query(Person)
            .order_by(Person.person_id)
            .join("paperwork", aliased=True)
            .filter(Paperwork.description.like("%#2%"))
            .all(),
            [e1, m1],
        ) 

def test_join_from_polymorphic_explicit_aliased_one(self):
        sess = create_session()
        pa = aliased(Paperwork)
        eq_(
            sess.query(Person)
            .order_by(Person.person_id)
            .join(pa, "paperwork")
            .filter(pa.description.like("%review%"))
            .all(),
            [b1, m1],
        ) 

def test_join_from_polymorphic_flag_aliased_one(self):
        sess = create_session()
        eq_(
            sess.query(Person)
            .order_by(Person.person_id)
            .join("paperwork", aliased=True)
            .filter(Paperwork.description.like("%review%"))
            .all(),
            [b1, m1],
        ) 

def test_join_from_polymorphic_nonaliased_four(self):
        sess = create_session()
        eq_(
            sess.query(Person)
            .order_by(Person.person_id)
            .join("paperwork")
            .filter(Person.name.like("%dog%"))
            .filter(Paperwork.description.like("%#2%"))
            .all(),
            [m1],
        ) 

def test_join_from_polymorphic_nonaliased_two(self):
        sess = create_session()
        eq_(
            sess.query(Person)
            .order_by(Person.person_id)
            .join("paperwork")
            .filter(Paperwork.description.like("%#2%"))
            .all(),
            [e1, m1],
        ) 

def test_join_from_polymorphic_nonaliased_one_future(self):
        sess = create_session()
        eq_(
            sess.execute(
                future_select(Person)
                .join(Person.paperwork)
                .filter(Paperwork.description.like("%review%"))
            )
            .unique()
            .scalars()
            .all(),
            [b1, m1],
        ) 

def test_join_from_polymorphic_nonaliased_one(self):
        sess = create_session()
        eq_(
            sess.query(Person)
            .join("paperwork")
            .filter(Paperwork.description.like("%review%"))
            .all(),
            [b1, m1],
        ) 

def test_multi_join(self):
        sess = create_session()
        e = aliased(Person)
        c = aliased(Company)
        q = (
            sess.query(Company, Person, c, e)
            .join(Person, Company.employees)
            .join(e, c.employees)
            .filter(Person.person_id != e.person_id)
            .filter(Person.name == "dilbert")
            .filter(e.name == "wally")
        )
        eq_(q.count(), 1)
        eq_(
            q.all(),
            [
                (
                    Company(company_id=1, name="MegaCorp, Inc."),
                    Engineer(
                        status="regular engineer",
                        engineer_name="dilbert",
                        name="dilbert",
                        company_id=1,
                        primary_language="java",
                        person_id=1,
                        type="engineer",
                    ),
                    Company(company_id=1, name="MegaCorp, Inc."),
                    Engineer(
                        status="regular engineer",
                        engineer_name="wally",
                        name="wally",
                        company_id=1,
                        primary_language="c++",
                        person_id=2,
                        type="engineer",
                    ),
                )
            ],
        ) 

def test_join_to_subclass(self):
        sess = create_session()

        # TODO: these should all be deprecated (?) - these joins are on the
        # core tables and should not be getting adapted, not sure why
        # adaptation is happening? (is it?)  emit a warning when the adaptation
        # occurs?

        eq_(
            sess.query(Company)
            .join(people.join(engineers), "employees")
            .filter(Engineer.primary_language == "java")
            .all(),
            [c1],
        )