Method test_mixed_where

test/sql/test_query.py:1760–1821

Joins t1->t2, outer t2->t3, plus a where on each table in turn.

(self)

            self.assertRows(expr, [(10, 20, 30), (11, 21, None)])

    def test_mixed_where(self):
        """Joins t1->t2, outer t2->t3, plus a where on each table in turn."""
        t1, t2, t3 = self.tables("t1", "t2", "t3")

        for criteria in (t2.c.t2_id == t3.c.t2_id, t3.c.t2_id == t2.c.t2_id):
            expr = (
                select(t1.c.t1_id, t2.c.t2_id, t3.c.t3_id)
                .where(
                    t1.c.name == "t1 #10",
                )
                .select_from(t1.join(t2).outerjoin(t3, criteria))
            )
            self.assertRows(expr, [(10, 20, 30)])

            expr = (
                select(t1.c.t1_id, t2.c.t2_id, t3.c.t3_id)
                .where(
                    t2.c.name == "t2 #20",
                )
                .select_from(t1.join(t2).outerjoin(t3, criteria))
            )
            self.assertRows(expr, [(10, 20, 30)])

            expr = (
                select(t1.c.t1_id, t2.c.t2_id, t3.c.t3_id)
                .where(
                    t3.c.name == "t3 #30",
                )
                .select_from(t1.join(t2).outerjoin(t3, criteria))
            )
            self.assertRows(expr, [(10, 20, 30)])

            expr = (
                select(t1.c.t1_id, t2.c.t2_id, t3.c.t3_id)
                .where(
                    and_(t1.c.name == "t1 #10", t2.c.name == "t2 #20"),
                )
                .select_from(t1.join(t2).outerjoin(t3, criteria))
            )
            self.assertRows(expr, [(10, 20, 30)])

            expr = (
                select(t1.c.t1_id, t2.c.t2_id, t3.c.t3_id)
                .where(
                    and_(t2.c.name == "t2 #20", t3.c.name == "t3 #30"),
                )
                .select_from(t1.join(t2).outerjoin(t3, criteria))
            )
            self.assertRows(expr, [(10, 20, 30)])

            expr = (
                select(t1.c.t1_id, t2.c.t2_id, t3.c.t3_id)
                .where(
                    and_(
                        t1.c.name == "t1 #10",
                        t2.c.name == "t2 #20",
                        t3.c.name == "t3 #30",
                    ),