Python sqlalchemy.orm.join() Examples

The following are 30 code examples of sqlalchemy.orm.join(). You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may also want to check out all available functions/classes of the module sqlalchemy.orm , or try the search function .
Example #1
Source File: test_core_compilation.py    From sqlalchemy with MIT License 6 votes vote down vote up
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) 
Example #2
Source File: util.py    From jbox with MIT License 6 votes vote down vote up
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
        ) 
Example #3
Source File: test_core_compilation.py    From sqlalchemy with MIT License 6 votes vote down vote up
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) 
Example #4
Source File: test_core_compilation.py    From sqlalchemy with MIT License 6 votes vote down vote up
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) 
Example #5
Source File: util.py    From pyRevit with GNU General Public License v3.0 6 votes vote down vote up
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
        ) 
Example #6
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 6 votes vote down vote up
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],
        ) 
Example #7
Source File: util.py    From Fluid-Designer with GNU General Public License v3.0 6 votes vote down vote up
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
        ) 
Example #8
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 6 votes vote down vote up
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,
        ) 
Example #9
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 6 votes vote down vote up
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,
        ) 
Example #10
Source File: util.py    From planespotter with MIT License 6 votes vote down vote up
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
        ) 
Example #11
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 6 votes vote down vote up
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],
        ) 
Example #12
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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],
        ) 
Example #13
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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,
        ) 
Example #14
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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],
        ) 
Example #15
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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],
        ) 
Example #16
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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,
        ) 
Example #17
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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,)],
        ) 
Example #18
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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,
        ) 
Example #19
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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,
        ) 
Example #20
Source File: test_core_compilation.py    From sqlalchemy with MIT License 5 votes vote down vote up
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) 
Example #21
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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],
        ) 
Example #22
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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],
        ) 
Example #23
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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],
        ) 
Example #24
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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],
        ) 
Example #25
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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],
        ) 
Example #26
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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],
        ) 
Example #27
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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],
        ) 
Example #28
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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],
        ) 
Example #29
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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",
                    ),
                )
            ],
        ) 
Example #30
Source File: test_polymorphic_rel.py    From sqlalchemy with MIT License 5 votes vote down vote up
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],
        )