Python sqlalchemy.orm.subqueryload() Examples

def test_caches_query_per_base_subq(self):
        Foo, Bar, Baz, Related = (
            self.classes.Foo,
            self.classes.Bar,
            self.classes.Baz,
            self.classes.Related,
        )
        s = Session(testing.db)

        def go():
            eq_(
                s.query(Foo)
                .with_polymorphic([Bar, Baz])
                .order_by(Foo.id)
                .options(subqueryload(Foo.related))
                .all(),
                [
                    Bar(id=1, related=Related(id=1)),
                    Bar(id=2, related=Related(id=2)),
                    Baz(id=3, related=Related(id=1)),
                    Baz(id=4, related=Related(id=2)),
                ],
            )

        self.assert_sql_count(testing.db, go, 2) 

def test_subq_through_related_aliased(self):
        Parent = self.classes.Parent
        Base = self.classes.Base
        pa = aliased(Parent)
        sess = Session()

        def go():
            eq_(
                sess.query(pa)
                .options(subqueryload(pa.children).subqueryload(Base.related))
                .order_by(pa.data)
                .all(),
                [p1, p2],
            )

        self.assert_sql_count(testing.db, go, 3) 

def test_subq_through_related(self):
        Parent = self.classes.Parent
        Base = self.classes.Base
        sess = Session()

        def go():
            eq_(
                sess.query(Parent)
                .options(
                    subqueryload(Parent.children).subqueryload(Base.related)
                )
                .order_by(Parent.data)
                .all(),
                [p1, p2],
            )

        self.assert_sql_count(testing.db, go, 3) 

def test_subqueryload(self):
        Subparent = self.classes.Subparent

        sess = create_session()

        def go():
            eq_(
                sess.query(Subparent)
                .options(subqueryload(Subparent.children))
                .all(),
                [p1, p2],
            )

        self.assert_sql_count(testing.db, go, 2)

        sess.expunge_all()

        def go():
            eq_(
                sess.query(Subparent).options(subqueryload("children")).all(),
                [p1, p2],
            )

        self.assert_sql_count(testing.db, go, 2) 

def test_free_w_poly_subquery(self):
        A = self.classes.A
        B = self.classes.B
        C = self.classes.C
        D = self.classes.D

        session = Session()
        d = session.query(D).one()
        a_poly = with_polymorphic(A, [B, C])

        def go():
            for a in session.query(a_poly).options(
                subqueryload(a_poly.B.related), subqueryload(a_poly.C.related)
            ):
                eq_(a.related, [d])

        self.assert_sql_count(testing.db, go, 3) 

def trips(self, fltr=None, prefetch_stop_times=True, prefetch_routes=False, prefetch_stops=False, prefetch_calendars=False, batch_size=800):
        idquery = self._session.query(Trip.feed_id, Trip.trip_id).distinct()
        if fltr is not None:
            idquery = _AutoJoiner(self._orm, idquery, fltr).autojoin()
            idquery = idquery.filter(fltr)
        # Only query IDs first
        tripids = idquery.all()
        def query_factory():
            query = self._session.query(Trip)
            _prefetch_stop_times = prefetch_stop_times
            if prefetch_stops:
                _prefetch_stop_times = True
            if _prefetch_stop_times:
                loadopt = subqueryload('stop_times')
                if prefetch_stops:
                    loadopt = loadopt.subqueryload('stop')
                query = query.options(loadopt)
            if prefetch_routes:
                query = query.options(subqueryload('route'))
            if prefetch_calendars:
                query = query.options(subqueryload('calendar'))
            return query
        return self._page_query(query_factory, Trip.feed_id, Trip.trip_id, tripids, batch_size) 

def with_subquery(cls, *paths):
        """
        Eagerload for simple cases where we need to just
         joined load some relations
        In strings syntax, you can split relations with dot 
         (it's SQLAlchemy feature)

        :type paths: *List[str] | *List[InstrumentedAttribute]

        Example 1:
            User.with_subquery('posts', 'posts.comments').all()

        Example 2:
            User.with_subquery(User.posts, User.comments).all()
        """
        options = [subqueryload(path) for path in paths]
        return cls.query.options(*options) 

def stoptimes(self, fltr=None, prefetch_trips=True, prefetch_stop_times=False):
        query = self._session.query(StopTime).distinct()
        if fltr is not None:
            query = _AutoJoiner(self._orm, query, fltr).autojoin()
            query = query.filter(fltr)
        if prefetch_stop_times:
            prefetch_trips = True
        if prefetch_trips:
            loadopt = subqueryload('trip')
            if prefetch_stop_times:
                loadopt = loadopt.subqueryload('stop_times')
            query = query.options(loadopt)
        # Note: ID batching would be difficult to implement for StopTime
        # as StopTime do have a composite-primary composed of 3 elements
        # and 2 of them (trip_id + stop_seq) can't be grouped easily.
        return query.all() 

def test_uselist_false_warning(self):
        """test that multiple rows received by a
        uselist=False raises a warning."""

        User, users, orders, Order = (
            self.classes.User,
            self.tables.users,
            self.tables.orders,
            self.classes.Order,
        )

        mapper(
            User,
            users,
            properties={"order": relationship(Order, uselist=False)},
        )
        mapper(Order, orders)
        s = create_session()
        assert_raises(
            sa.exc.SAWarning,
            s.query(User).options(subqueryload(User.order)).all,
        ) 

def test_loads_second_level_collection_to_scalar(self):
        User, Address, Dingaling, sess = self._collection_to_scalar_fixture()

        u1 = sess.query(User).get(8)
        a1 = Address()
        u1.addresses.append(a1)
        a2 = u1.addresses[0]
        a2.email_address = "foo"
        sess.query(User).options(
            subqueryload("addresses").subqueryload("dingaling")
        ).filter_by(id=8).all()
        assert u1.addresses[-1] is a1
        for a in u1.addresses:
            if a is not a1:
                assert "dingaling" in a.__dict__
            else:
                assert "dingaling" not in a.__dict__
            if a is a2:
                eq_(a2.email_address, "foo") 

def test_primary_eager_aliasing_subqueryload(self):
        # test that subqueryload does not occur because the parent
        # row cannot support it
        sess = create_session()

        def go():
            eq_(
                sess.query(Person)
                .order_by(Person.person_id)
                .options(subqueryload(Engineer.machines))
                .all(),
                all_employees,
            )

        count = {"": 14, "Polymorphic": 7}.get(self.select_type, 8)
        self.assert_sql_count(testing.db, go, count) 

def test_primary_eager_aliasing_joinedload(self):
        # For both joinedload() and subqueryload(), if the original q is
        # not loading the subclass table, the joinedload doesn't happen.

        sess = create_session()

        def go():
            eq_(
                sess.query(Person)
                .order_by(Person.person_id)
                .options(joinedload(Engineer.machines))[1:3],
                all_employees[1:3],
            )

        count = {"": 6, "Polymorphic": 3}.get(self.select_type, 4)
        self.assert_sql_count(testing.db, go, count) 

def fare_attributes(self, fltr=None, prefetch_fare_rules=True):
        query = self._session.query(FareAttribute).distinct()
        if fltr is not None:
            # TODO _AutoJoiner will not work here
            # The join / filter to deduce from a general filter expression
            # is rather complex, as there are many paths from a fare attribute
            # to any object. For example, 4 paths for an agency:
            # Path 1: farerule-route-agency,
            # Path 2/3/4: farerule-zone[origin,destination,contains]-stop-stoptime-trip-route-agency
            # BUT we also sometimes need to include any fare_attributes containing rules that does
            # not correspond to any entities, as they are the default...
            # query = query.join(FareRule).join(Route, FareRule.route).filter(fltr)
            query = query.filter(fltr)
        if prefetch_fare_rules:
            query = query.options(subqueryload('fare_rules'))
        return query.all() 

def test_integrate(self):
        Director = self.classes.Director
        Movie = self.classes.Movie

        session = Session(testing.db)
        rscott = Director(name="Ridley Scott")
        alien = Movie(title="Alien")
        brunner = Movie(title="Blade Runner")
        rscott.movies.append(brunner)
        rscott.movies.append(alien)
        session.add_all([rscott, alien, brunner])
        session.commit()

        close_all_sessions()
        d = session.query(Director).options(subqueryload("*")).first()  # noqa
        assert len(list(session)) == 3 

def test_twolevel_subqueryload_wsubclass(self):
        ParentThing, DataContainer, SubJob = (
            self.classes.ParentThing,
            self.classes.DataContainer,
            self.classes.SubJob,
        )
        s = Session(testing.db)
        q = s.query(ParentThing).options(
            subqueryload(ParentThing.container).subqueryload(
                DataContainer.jobs.of_type(SubJob)
            )
        )

        def go():
            eq_(q.all(), self._fixture())

        self.assert_sql_count(testing.db, go, 7) 

def test_o2m(self):
        A, A2, B, C1o2m, C2o2m = self.classes("A", "A2", "B", "C1o2m", "C2o2m")

        s = Session()

        # A -J-> B -L-> C1
        # A -J-> B -S-> C2

        # A -J-> A2 -J-> B -S-> C1
        # A -J-> A2 -J-> B -L-> C2

        q = s.query(A).options(
            joinedload(A.b).subqueryload(B.c2_o2m),
            joinedload(A.a2).joinedload(A2.b).subqueryload(B.c1_o2m),
        )

        a1 = q.all()[0]

        is_true("c1_o2m" in a1.b.__dict__)
        is_true("c2_o2m" in a1.b.__dict__) 

def get_all_API_list(self, session, pagination_helper=None, **filters):
        deleted = filters.pop('show_deleted', True)
        query = session.query(self.model_class).filter_by(
            **filters)

        query = query.options(
            subqueryload(models.L7Policy.l7rules),
            subqueryload(models.L7Policy.listener),
            subqueryload(models.L7Policy.redirect_pool),
            subqueryload(models.L7Policy._tags),
            noload('*'))

        if not deleted:
            query = query.filter(
                self.model_class.provisioning_status != consts.DELETED)

        if pagination_helper:
            model_list, links = pagination_helper.apply(
                query, self.model_class)
        else:
            links = None
            model_list = query.order_by(self.model_class.position).all()

        data_model_list = [model.to_data_model() for model in model_list]
        return data_model_list, links 

def get_all_API_list(self, session, pagination_helper=None, **filters):
        """Get a list of L7 Rules for the API list call.

        This get_all returns a data set that is only one level deep
        in the data graph. This is an optimized query for the API L7 Rule
        list method.

        :param session: A Sql Alchemy database session.
        :param pagination_helper: Helper to apply pagination and sorting.
        :param filters: Filters to decide which entities should be retrieved.
        :returns: [octavia.common.data_model]
        """

        # sub-query load the tables we need
        # no-load (blank) the tables we don't need
        query_options = (
            subqueryload(models.L7Rule.l7policy),
            subqueryload(models.L7Rule._tags),
            noload('*'))

        return super(L7RuleRepository, self).get_all(
            session, pagination_helper=pagination_helper,
            query_options=query_options, **filters) 

def test_m2o(self):
        A, A2, B, C1m2o, C2m2o = self.classes("A", "A2", "B", "C1m2o", "C2m2o")

        s = Session()

        # A -J-> B -L-> C1
        # A -J-> B -S-> C2

        # A -J-> A2 -J-> B -S-> C1
        # A -J-> A2 -J-> B -L-> C2

        q = s.query(A).options(
            joinedload(A.b).subqueryload(B.c2_m2o),
            joinedload(A.a2).joinedload(A2.b).subqueryload(B.c1_m2o),
        )

        a1 = q.all()[0]
        is_true("c1_m2o" in a1.b.__dict__)
        is_true("c2_m2o" in a1.b.__dict__) 

def get_all_API_list(self, session, pagination_helper=None, **filters):
        """Get a list of members for the API list call.

        This get_all returns a data set that is only one level deep
        in the data graph. This is an optimized query for the API member
        list method.

        :param session: A Sql Alchemy database session.
        :param pagination_helper: Helper to apply pagination and sorting.
        :param filters: Filters to decide which entities should be retrieved.
        :returns: [octavia.common.data_model]
        """

        # sub-query load the tables we need
        # no-load (blank) the tables we don't need
        query_options = (
            subqueryload(models.Member.pool),
            subqueryload(models.Member._tags),
            noload('*'))

        return super(MemberRepository, self).get_all(
            session, pagination_helper=pagination_helper,
            query_options=query_options, **filters) 

def get_all_API_list(self, session, pagination_helper=None, **filters):
        """Get a list of health monitors for the API list call.

        This get_all returns a data set that is only one level deep
        in the data graph. This is an optimized query for the API health
        monitor list method.

        :param session: A Sql Alchemy database session.
        :param pagination_helper: Helper to apply pagination and sorting.
        :param filters: Filters to decide which entities should be retrieved.
        :returns: [octavia.common.data_model]
        """

        # sub-query load the tables we need
        # no-load (blank) the tables we don't need
        query_options = (
            subqueryload(models.HealthMonitor.pool),
            subqueryload(models.HealthMonitor._tags),
            noload('*'))

        return super(HealthMonitorRepository, self).get_all(
            session, pagination_helper=pagination_helper,
            query_options=query_options, **filters) 

def test_subqueryload_on_joined_noload(self):
        Parent = self.classes.Parent
        Child = self.classes.Child

        s = Session()

        # here we have
        # Parent->subqueryload->Child->joinedload->parent->noload->children.
        # the actual subqueryload has to emit *after* we've started populating
        # Parent->subqueryload->child.
        parent = s.query(Parent).options([subqueryload("children")]).first()
        eq_(parent.children, [Child(name="c1")]) 

def test_chained_subq_subclass(self):
        s = Session()
        q = s.query(Company).options(
            subqueryload(Company.employees.of_type(Engineer))
            .subqueryload(Engineer.machines)
            .subqueryload(Machine.type)
        )

        def go():
            eq_(q.all(), [self._fixture()])

        self.assert_sql_count(testing.db, go, 4) 

def test_from_subclass(self):
        Director = self.classes.Director

        s = create_session()

        with self.sql_execution_asserter(testing.db) as asserter:
            s.query(Director).options(subqueryload("*")).all()
        asserter.assert_(
            CompiledSQL(
                "SELECT director.id AS director_id, "
                "persistent.id AS persistent_id, director.name "
                "AS director_name FROM persistent JOIN director "
                "ON persistent.id = director.id"
            ),
            CompiledSQL(
                "SELECT movie.id AS movie_id, "
                "persistent.id AS persistent_id, "
                "movie.director_id AS movie_director_id, "
                "movie.title AS movie_title, "
                "anon_1.director_id AS anon_1_director_id "
                "FROM (SELECT director.id AS director_id "
                "FROM persistent JOIN director "
                "ON persistent.id = director.id) AS anon_1 "
                "JOIN (persistent JOIN movie "
                "ON persistent.id = movie.id) "
                "ON anon_1.director_id = movie.director_id",
            ),
        ) 

def test_subqueryload_explicit_withpoly(self):
        sess = Session()

        def go():
            target = with_polymorphic(Person, Engineer)
            eq_(
                sess.query(Company)
                .filter_by(company_id=1)
                .options(subqueryload(Company.employees.of_type(target)))
                .all(),
                [self._company_with_emps_fixture()[0]],
            )

        self.assert_sql_count(testing.db, go, 4) 

def test_subquery_load(self):
        Child1, Child2 = self.classes.Child1, self.classes.Child2
        sess = create_session()
        c1 = Child1()
        c1.left_child2 = Child2()
        sess.add(c1)
        sess.flush()
        sess.expunge_all()

        query_ = sess.query(Child1).options(subqueryload("left_child2"))
        for row in query_.all():
            assert row.left_child2 

def test_subqueryload_on_subclass(self):
        sess = create_session()
        expected = [
            Engineer(
                name="dilbert",
                engineer_name="dilbert",
                primary_language="java",
                status="regular engineer",
                machines=[
                    Machine(name="IBM ThinkPad"),
                    Machine(name="IPhone"),
                ],
            )
        ]

        def go():
            wp = with_polymorphic(Person, "*")
            eq_(
                sess.query(wp)
                .options(subqueryload(wp.Engineer.machines))
                .filter(wp.name == "dilbert")
                .all(),
                expected,
            )

            # the old version of this test has never worked, apparently,
            # was always spitting out a cartesian product.  Since we
            # are getting rid of query.with_polymorphic() is it not
            # worth fixing.
            # eq_(
            #    sess.query(Person)
            #    .with_polymorphic("*")
            #    .options(subqueryload(Engineer.machines))
            #    .filter(Person.name == "dilbert")
            #    .all(),
            #    expected,
            # )

        self.assert_sql_count(testing.db, go, 2) 

def test_relationship_to_polymorphic_three(self):
        expected = self._company_with_emps_machines_fixture()
        sess = create_session()

        sess = create_session()

        def go():
            eq_(
                sess.query(Company)
                .options(
                    subqueryload(
                        Company.employees.of_type(Engineer)
                    ).subqueryload(Engineer.machines)
                )
                .all(),
                expected,
            )

        # the old case where subqueryload_all
        # didn't work with of_tyoe
        # count = { '':8, 'Joins':4, 'Unions':4, 'Polymorphic':3,
        #    'AliasedJoins':4}[self.select_type]

        # query one is company->Person/Engineer->Machines
        # query two is Person/Engineer subq
        # query three is Machines subq
        # (however this test can't tell if the Q was a
        # lazyload or subqload ...)
        # query four is managers + boss for row #3
        # query five is managers for row #4
        count = 5
        self.assert_sql_count(testing.db, go, count) 

def test_subquery_wsubclass(self):
        DataContainer, SubJob = (
            self.classes.DataContainer,
            self.classes.SubJob,
        )
        s = Session(testing.db)
        q = s.query(DataContainer).options(
            subqueryload(DataContainer.jobs.of_type(SubJob))
        )

        def go():
            eq_(q.all(), self._dc_fixture())

        self.assert_sql_count(testing.db, go, 6) 

def test_subq_w_from_self_two(self):

        A, B, C = self.classes("A", "B", "C")

        s = Session()
        cache = {}

        for i in range(3):

            def go():
                q = (
                    s.query(B)
                    .execution_options(compiled_cache=cache)
                    .join(B.a)
                    .from_self()
                )
                q = q.options(subqueryload(B.ds))

                q.all()

            self.assert_sql_execution(
                testing.db,
                go,
                CompiledSQL(
                    "SELECT anon_1.b_id AS anon_1_b_id, anon_1.b_a_id AS "
                    "anon_1_b_a_id FROM (SELECT b.id AS b_id, b.a_id "
                    "AS b_a_id FROM b JOIN a ON a.id = b.a_id) AS anon_1"
                ),
                CompiledSQL(
                    "SELECT d.id AS d_id, d.b_id AS d_b_id, "
                    "anon_1.anon_2_b_id AS anon_1_anon_2_b_id "
                    "FROM (SELECT anon_2.b_id AS anon_2_b_id FROM "
                    "(SELECT b.id AS b_id, b.a_id AS b_a_id FROM b "
                    "JOIN a ON a.id = b.a_id) AS anon_2) AS anon_1 "
                    "JOIN d ON anon_1.anon_2_b_id = d.b_id ORDER BY d.id"
                ),
            )
            s.close()