Python sqlalchemy.orm.contains_eager() Examples

def test_columns_multi_table_uselabels_cols_contains_eager(self):
        # test that columns using column._label match, as well as that
        # ordering doesn't matter.
        User = self.classes.User
        Address = self.classes.Address

        s = create_session()
        q = (
            s.query(User)
            .from_statement(
                text(
                    "select users.name AS users_name, users.id AS users_id, "
                    "addresses.id AS addresses_id FROM users JOIN addresses "
                    "ON users.id = addresses.user_id WHERE users.id=8 "
                    "ORDER BY addresses.id"
                ).columns(User.name, User.id, Address.id)
            )
            .options(contains_eager(User.addresses))
        )

        def go():
            r = q.all()
            eq_(r[0].addresses, [Address(id=2), Address(id=3), Address(id=4)])

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

def get_dragents_hosting_bgp_speakers(self, context, bgp_speaker_ids,
                                          active=None, admin_state_up=None):
        query = context.session.query(BgpSpeakerDrAgentBinding)
        query = query.options(orm.contains_eager(
                              BgpSpeakerDrAgentBinding.dragent))
        query = query.join(BgpSpeakerDrAgentBinding.dragent)

        if len(bgp_speaker_ids) == 1:
            query = query.filter(
                BgpSpeakerDrAgentBinding.bgp_speaker_id == (
                    bgp_speaker_ids[0]))
        elif bgp_speaker_ids:
            query = query.filter(
                BgpSpeakerDrAgentBinding.bgp_speaker_id in bgp_speaker_ids)
        if admin_state_up is not None:
            query = query.filter(agent_model.Agent.admin_state_up ==
                                 admin_state_up)

        return [binding.dragent
                for binding in query
                if as_db.AgentSchedulerDbMixin.is_eligible_agent(
                                                active, binding.dragent)] 

def test_contains_eager_two(self):
        users, addresses, User = (
            self.tables.users,
            self.tables.addresses,
            self.classes.User,
        )

        sess = create_session()

        adalias = addresses.alias()
        q = (
            sess.query(User)
            .select_entity_from(users.outerjoin(adalias))
            .options(contains_eager(User.addresses, alias=adalias))
            .order_by(User.id, adalias.c.id)
        )

        def go():
            eq_(self.static.user_address_result, q.all())

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

def test_poly_query_on_correlate_except(self):
        Common, Superclass = self._fixture(True)

        poly = with_polymorphic(Superclass, "*")

        s = Session()
        q = (
            s.query(poly)
            .options(contains_eager(poly.common_relationship))
            .join(poly.common_relationship)
            .filter(Common.id == 1)
        )

        self.assert_compile(
            q,
            "SELECT c.id AS c_id, (SELECT count(s1.id) AS count_1 "
            "FROM s1 LEFT OUTER JOIN s2 ON s1.id = s2.id "
            "WHERE s1.common_id = c.id) AS anon_1, "
            "s1.id AS s1_id, "
            "s1.common_id AS s1_common_id, "
            "s1.discriminator_field AS s1_discriminator_field, "
            "s2.id AS s2_id FROM s1 "
            "LEFT OUTER JOIN s2 ON s1.id = s2.id "
            "JOIN c ON c.id = s1.common_id WHERE c.id = :id_1",
        ) 

def test_contains_eager_outermost(self, plain_fixture):
        User, Address = plain_fixture

        stmt = (
            select(Address)
            .join(Address.user)
            .options(contains_eager(Address.user))
        )

        # render joined eager loads with stringify
        self.assert_compile(
            stmt,
            "SELECT users.id, users.name, addresses.id AS id_1, "
            "addresses.user_id, "
            "addresses.email_address "
            "FROM addresses JOIN users ON users.id = addresses.user_id",
        ) 

def test_with_polymorphic_join_exec_contains_eager_one(self):
        sess = Session()

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

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

def test_contains_eager_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)
            .join(DataContainer.jobs.of_type(Job_P))
            .options(contains_eager(DataContainer.jobs.of_type(Job_P)))
        )

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

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

def test_multi_path_load_of_type(self):
        A, B, C, D = self.classes("A", "B", "C", "D")

        s = Session()

        c = C(d=D())

        s.add(A(b=B(c=c), c=c))
        s.commit()

        c_alias_1 = aliased(C)
        c_alias_2 = aliased(C)

        q = s.query(A)
        q = q.join(A.b).join(B.c.of_type(c_alias_1)).join(c_alias_1.d)
        q = q.options(
            contains_eager(A.b)
            .contains_eager(B.c.of_type(c_alias_1))
            .contains_eager(c_alias_1.d)
        )
        q = q.join(A.c.of_type(c_alias_2))
        q = q.options(contains_eager(A.c.of_type(c_alias_2)))

        a1 = q.all()[0]

        # ensure 'd' key was populated in dict.  Varies based on
        # PYTHONHASHSEED
        in_("d", a1.c.__dict__) 

def get_inventory(self, context, inventory_id):
        session = get_session()
        query = model_query(models.Inventory, session=session)
        query = query.join(models.Inventory.resource_provider)
        query = query.options(contains_eager('resource_provider'))
        query = query.filter_by(id=inventory_id)
        try:
            return query.one()
        except NoResultFound:
            raise exception.InventoryNotFound(inventory=inventory_id) 

def test_multi_path_load_legacy_join_style(self):
        User, LD, A, LDA = self.classes("User", "LD", "A", "LDA")

        s = Session()

        u0 = User(data=42)
        l0 = LD(user=u0)
        z0 = A(ld=l0)
        lz0 = LDA(ld=l0, a=z0)
        s.add_all([u0, l0, z0, lz0])
        s.commit()

        l_ac = aliased(LD)
        u_ac = aliased(User)

        # these paths don't work out correctly?
        lz_test = (
            s.query(LDA)
            .join("ld")
            .options(contains_eager("ld"))
            .join("a", (l_ac, "ld"), (u_ac, "user"))
            .options(
                contains_eager("a")
                .contains_eager("ld", alias=l_ac)
                .contains_eager("user", alias=u_ac)
            )
            .first()
        )

        in_("user", lz_test.a.ld.__dict__) 

def test_multi_path_load_of_type(self):
        User, LD, A, LDA = self.classes("User", "LD", "A", "LDA")

        s = Session()

        u0 = User(data=42)
        l0 = LD(user=u0)
        z0 = A(ld=l0)
        lz0 = LDA(ld=l0, a=z0)
        s.add_all([u0, l0, z0, lz0])
        s.commit()

        l_ac = aliased(LD)
        u_ac = aliased(User)

        lz_test = (
            s.query(LDA)
            .join(LDA.ld)
            .options(contains_eager(LDA.ld))
            .join(LDA.a)
            .join(LDA.ld.of_type(l_ac))
            .join(l_ac.user.of_type(u_ac))
            .options(
                contains_eager(LDA.a),
                contains_eager(LDA.ld.of_type(l_ac)).contains_eager(
                    l_ac.user.of_type(u_ac)
                ),
            )
            .first()
        )

        in_("user", lz_test.a.ld.__dict__) 

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

        sess = create_session()

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

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

        sess.expunge_all()

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

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

def test_contains_eager_multi_alias(self):
        X, B, A = self.classes("X", "B", "A")
        s = Session()

        a_b_alias = aliased(B, name="a_b")
        b_x_alias = aliased(X, name="b_x")

        q = (
            s.query(A)
            .outerjoin(A.b.of_type(a_b_alias))
            .outerjoin(a_b_alias.x.of_type(b_x_alias))
            .options(
                contains_eager(A.b.of_type(a_b_alias)).contains_eager(
                    a_b_alias.x.of_type(b_x_alias)
                )
            )
        )
        self.assert_compile(
            q,
            "SELECT b_x.id AS b_x_id, b_x.a_id AS b_x_a_id, a_b.id AS a_b_id, "
            "a_b.kind AS a_b_kind, a_b.a_id AS a_b_a_id, a.id AS a_id_1, "
            "a.kind AS a_kind, a.a_id AS a_a_id FROM a "
            "LEFT OUTER JOIN a AS a_b ON a.id = a_b.a_id AND a_b.kind IN "
            "([POSTCOMPILE_kind_1]) LEFT OUTER JOIN x AS b_x "
            "ON a_b.id = b_x.a_id",
        ) 

def test_poly_query_on_correlate(self):
        Common, Superclass = self._fixture(False)

        poly = with_polymorphic(Superclass, "*")

        s = Session()
        q = (
            s.query(poly)
            .options(contains_eager(poly.common_relationship))
            .join(poly.common_relationship)
            .filter(Common.id == 1)
        )

        # note the order of c.id, subquery changes based on if we
        # used correlate or correlate_except; this is only with the
        # patch in place.   Not sure why this happens.
        self.assert_compile(
            q,
            "SELECT c.id AS c_id, (SELECT count(s1.id) AS count_1 "
            "FROM s1 LEFT OUTER JOIN s2 ON s1.id = s2.id "
            "WHERE s1.common_id = c.id) AS anon_1, "
            "s1.id AS s1_id, "
            "s1.common_id AS s1_common_id, "
            "s1.discriminator_field AS s1_discriminator_field, "
            "s2.id AS s2_id FROM s1 "
            "LEFT OUTER JOIN s2 ON s1.id = s2.id "
            "JOIN c ON c.id = s1.common_id WHERE c.id = :id_1",
        ) 

def test_from_alias_two_needs_nothing(self):
        User, addresses, users = (
            self.classes.User,
            self.tables.addresses,
            self.tables.users,
        )

        query = (
            users.select(users.c.id == 7)
            .union(users.select(users.c.id > 7))
            .alias("ulist")
            .outerjoin(addresses)
            .select(
                use_labels=True, order_by=[text("ulist.id"), addresses.c.id]
            )
        )
        sess = create_session()
        q = sess.query(User)

        def go():
            result = (
                q.options(contains_eager("addresses"))
                .from_statement(query)
                .all()
            )
            assert self.static.user_address_result == result

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

def test_from_alias_three(self):
        User, addresses, users = (
            self.classes.User,
            self.tables.addresses,
            self.tables.users,
        )

        query = (
            users.select(users.c.id == 7)
            .union(users.select(users.c.id > 7))
            .alias("ulist")
            .outerjoin(addresses)
            .select(
                use_labels=True, order_by=[text("ulist.id"), addresses.c.id]
            )
        )
        sess = create_session()

        # better way.  use select_entity_from()
        def go():
            result = (
                sess.query(User)
                .select_entity_from(query.subquery())
                .options(contains_eager("addresses"))
                .all()
            )
            assert self.static.user_address_result == result

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

def test_from_alias_four(self):
        User, addresses, users = (
            self.classes.User,
            self.tables.addresses,
            self.tables.users,
        )

        sess = create_session()

        # same thing, but alias addresses, so that the adapter
        # generated by select_entity_from() is wrapped within
        # the adapter created by contains_eager()
        adalias = addresses.alias()
        query = (
            users.select(users.c.id == 7)
            .union(users.select(users.c.id > 7))
            .alias("ulist")
            .outerjoin(adalias)
            .select(use_labels=True, order_by=[text("ulist.id"), adalias.c.id])
        )

        def go():
            result = (
                sess.query(User)
                .select_entity_from(query.subquery())
                .options(contains_eager("addresses", alias=adalias))
                .all()
            )
            assert self.static.user_address_result == result

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

def test_contains_eager_one(self):
        addresses, User = (self.tables.addresses, self.classes.User)

        sess = create_session()

        # test that contains_eager suppresses the normal outer join rendering
        q = (
            sess.query(User)
            .outerjoin(User.addresses)
            .options(contains_eager(User.addresses))
            .order_by(User.id, addresses.c.id)
        )
        self.assert_compile(
            q.with_labels().statement,
            "SELECT addresses.id AS addresses_id, "
            "addresses.user_id AS addresses_user_id, "
            "addresses.email_address AS "
            "addresses_email_address, users.id AS "
            "users_id, users.name AS users_name FROM "
            "users LEFT OUTER JOIN addresses ON "
            "users.id = addresses.user_id ORDER BY "
            "users.id, addresses.id",
            dialect=default.DefaultDialect(),
        )

        def go():
            assert self.static.user_address_result == q.all()

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

def test_contains_eager_four(self):
        users, addresses, User = (
            self.tables.users,
            self.tables.addresses,
            self.classes.User,
        )

        sess = create_session()

        selectquery = users.outerjoin(addresses).select(
            users.c.id < 10,
            use_labels=True,
            order_by=[users.c.id, addresses.c.id],
        )

        q = sess.query(User)

        def go():
            result = (
                q.options(contains_eager("addresses"))
                .from_statement(selectquery)
                .all()
            )
            assert self.static.user_address_result[0:3] == result

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

def test_contains_eager_multi_alias(self):
        orders, items, users, order_items, User = (
            self.tables.orders,
            self.tables.items,
            self.tables.users,
            self.tables.order_items,
            self.classes.User,
        )

        sess = create_session()
        q = sess.query(User)

        oalias = orders.alias("o1")
        ialias = items.alias("i1")
        query = (
            users.outerjoin(oalias)
            .outerjoin(order_items)
            .outerjoin(ialias)
            .select(use_labels=True)
            .order_by(users.c.id, oalias.c.id, ialias.c.id)
        )

        # test using Alias with more than one level deep

        # new way:
        # from sqlalchemy.orm.strategy_options import Load
        # opt = Load(User).contains_eager('orders', alias=oalias).
        #     contains_eager('items', alias=ialias)

        def go():
            result = list(
                q.options(
                    contains_eager("orders", alias=oalias),
                    contains_eager("orders.items", alias=ialias),
                ).from_statement(query)
            )
            assert self.static.user_order_result == result

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

def test_contains_eager_multi_aliased(self):
        Item, User, Order = (
            self.classes.Item,
            self.classes.User,
            self.classes.Order,
        )

        sess = create_session()
        q = sess.query(User)

        # test using Aliased with more than one level deep
        oalias = aliased(Order)
        ialias = aliased(Item)

        def go():
            result = (
                q.options(
                    contains_eager(User.orders, alias=oalias),
                    contains_eager(User.orders, Order.items, alias=ialias),
                )
                .outerjoin(oalias, User.orders)
                .outerjoin(ialias, oalias.items)
                .order_by(User.id, oalias.id, ialias.id)
            )
            assert self.static.user_order_result == result.all()

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

def test_contains_eager_multi_aliased_of_type(self):
        # test newer style that does not use the alias parameter
        Item, User, Order = (
            self.classes.Item,
            self.classes.User,
            self.classes.Order,
        )

        sess = create_session()
        q = sess.query(User)

        # test using Aliased with more than one level deep
        oalias = aliased(Order)
        ialias = aliased(Item)

        def go():
            result = (
                q.options(
                    contains_eager(User.orders.of_type(oalias)).contains_eager(
                        oalias.items.of_type(ialias)
                    )
                )
                .outerjoin(User.orders.of_type(oalias))
                .outerjoin(oalias.items.of_type(ialias))
                .order_by(User.id, oalias.id, ialias.id)
            )
            assert self.static.user_order_result == result.all()

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

def get_localized_message(login_id, message_id):
    '''
    Get message localization for language of a specific user.

    If translation for language of a user doesn't exist English translation is given.
    '''
    with session_scope() as session:
        localized_message = session.query(model.MessageLocalization).\
        from_statement(text("select lm.*, m.* from get_localized_message(:login_id, :message_id) lm inner join message m on lm.message_id = m.id")).\
        params(login_id=login_id, message_id=message_id).\
        options(contains_eager(model.MessageLocalization.message)).\
        one_or_none()
    return localized_message 

def upgrade():
    from inbox.ignition import main_engine

    op.add_column('actionlog', sa.Column('type', sa.String(16)))

    # Update action_log entries
    from inbox.models import Namespace, Account, ActionLog
    from inbox.models.session import session_scope

    with session_scope() as db_session:
        q = db_session.query(ActionLog).join(Namespace).join(Account).\
            filter(ActionLog.status == 'pending',
                   Account.discriminator != 'easaccount').\
            options(contains_eager(ActionLog.namespace, Namespace.account))

        print 'Updating {} action_log entries'.format(q.count())

        for a in q.all():
            a.type = 'actionlog'

        db_session.commit()

    engine = main_engine(pool_size=1, max_overflow=0)
    if not engine.has_table('easaccount'):
        return

    op.create_table('easactionlog',
                    sa.Column('id', sa.Integer()),
                    sa.Column('secondary_status',
                              sa.Enum('pending', 'successful', 'failed'),
                              server_default='pending'),
                    sa.Column('secondary_retries', sa.Integer(),
                              nullable=False, server_default='0'),
                    sa.PrimaryKeyConstraint('id'),
                    sa.ForeignKeyConstraint(['id'], ['actionlog.id'],
                                            ondelete='CASCADE')) 

def get(self, revision: Revision):
        """
        Return a list of style violations for a given revision.
        """
        build = fetch_build_for_revision(revision)
        if not build:
            return self.respond(status=404)

        build_ids = [original.id for original in build.original]

        query = (
            StyleViolation.query.options(contains_eager("job"))
            .join(Job, StyleViolation.job_id == Job.id)
            .filter(Job.build_id.in_(build_ids))
        )

        severity = request.args.get("severity")
        if severity:
            try:
                query = query.filter(
                    StyleViolation.severity == getattr(Severity, severity)
                )
            except AttributeError:
                raise NotImplementedError

        query = query.order_by(
            (StyleViolation.severity == Severity.error).desc(),
            StyleViolation.filename.asc(),
            StyleViolation.lineno.asc(),
            StyleViolation.colno.asc(),
        )

        return self.paginate_with_schema(styleviolation_schema, query) 

def list_inventories(self, context, filters=None, limit=None,
                         marker=None, sort_key=None, sort_dir=None):
        session = get_session()
        query = model_query(models.Inventory, session=session)
        query = self._add_inventories_filters(query, filters)
        query = query.join(models.Inventory.resource_provider)
        query = query.options(contains_eager('resource_provider'))
        return _paginate_query(models.Inventory, limit, marker,
                               sort_key, sort_dir, query) 

def list_allocations(self, context, filters=None, limit=None,
                         marker=None, sort_key=None, sort_dir=None):
        session = get_session()
        query = model_query(models.Allocation, session=session)
        query = self._add_allocations_filters(query, filters)
        query = query.join(models.Allocation.resource_provider)
        query = query.options(contains_eager('resource_provider'))
        return _paginate_query(models.Allocation, limit, marker,
                               sort_key, sort_dir, query) 

def get_allocation(self, context, allocation_id):
        session = get_session()
        query = model_query(models.Allocation, session=session)
        query = query.join(models.Allocation.resource_provider)
        query = query.options(contains_eager('resource_provider'))
        query = query.filter_by(id=allocation_id)
        try:
            return query.one()
        except NoResultFound:
            raise exception.AllocationNotFound(allocation=allocation_id) 

def with_my_activities(self):
        if not current_user or not current_user.is_authenticated:
            return self

        from .activity import Activity

        my_activities = aliased(Activity.query.filter(
            Activity.subject_id == current_user.id
            ).cte(name="my_activities"), "my_activities")

        return self.outerjoin(my_activities).options(contains_eager(
            Object.my_activities, alias=my_activities)) 

def create_query(self):
        """Called in the build_query() template method."""
        return DBSession.query(self.queried_model_class)

    ## commented out, as the necessary join is already in
    ## generate_query_results()
    #def query__client_join(self, query):
    #    """Called in the build_query() template method."""
    #    if self.client_relationship is not None:
    #        relationship_obj = getattr(self.queried_model_class,
    #                                   self.client_relationship)
    #        query = query.outerjoin(relationship_obj)
    #        query = query.options(contains_eager(relationship_obj))
    #    return query