Python sqlalchemy.orm.aliased() Examples

def _tenant_networks_by_network_query(self, context,
                                          network_id, bgp_speaker_id):
        """Return subquery for tenant networks by binding network ID"""
        address_scope = aliased(address_scope_db.AddressScope,
                                name='address_scope')
        router_attrs = aliased(l3_attrs_db.RouterExtraAttributes,
                               name='router_attrs')
        tenant_networks_query = context.session.query(
                                              l3_db.RouterPort.router_id,
                                              models_v2.Subnet.cidr,
                                              models_v2.Subnet.ip_version,
                                              address_scope.id)
        tenant_networks_query = tenant_networks_query.filter(
             l3_db.RouterPort.port_type != lib_consts.DEVICE_OWNER_ROUTER_GW,
             l3_db.RouterPort.port_type != lib_consts.DEVICE_OWNER_ROUTER_SNAT,
             l3_db.RouterPort.router_id == router_attrs.router_id,
             models_v2.IPAllocation.port_id == l3_db.RouterPort.port_id,
             models_v2.IPAllocation.subnet_id == models_v2.Subnet.id,
             models_v2.Subnet.network_id != network_id,
             models_v2.Subnet.subnetpool_id == models_v2.SubnetPool.id,
             models_v2.SubnetPool.address_scope_id == address_scope.id,
             BgpSpeaker.id == bgp_speaker_id,
             BgpSpeaker.ip_version == address_scope.ip_version,
             models_v2.Subnet.ip_version == address_scope.ip_version)
        return tenant_networks_query 

def get_sequences_for_project(project_id):
    """
    Return all sequences for given project and for which current user has
    a task assigned to a shot.
    """
    shot_type = shots_service.get_shot_type()
    sequence_type = shots_service.get_sequence_type()

    Shot = aliased(Entity, name="shot")
    query = (
        Entity.query.join(Shot, Shot.parent_id == Entity.id)
        .join(Task, Task.entity_id == Shot.id)
        .join(EntityType, EntityType.id == Entity.entity_type_id)
        .join(Project, Project.id == Entity.project_id)
        .join(ProjectStatus)
        .filter(Shot.entity_type_id == shot_type["id"])
        .filter(Entity.entity_type_id == sequence_type["id"])
        .filter(Project.id == project_id)
        .filter(build_assignee_filter())
        .filter(build_open_project_filter())
    )

    return Entity.serialize_list(query.all(), obj_type="Sequence") 

def test_orm_recursive_cte(pg_only_dburl):
    with S(pg_only_dburl, echo=ECHO) as s:
        # Start with "origins": books that don't have prequels
        seed = s.query(Book.id.label('id'), Book.id.label('origin')) \
            .filter(Book.prequel == None)

        # Recurse by picking up sequels
        sequel = aliased(Book, name='sequel')
        recursive = seed.cte(recursive=True)
        recursive = recursive.union(
            s.query(sequel.id, recursive.c.origin)
            .filter(sequel.prequel_id == recursive.c.id)
        )

        # Count total books per origin
        count = func.count().label('count')
        origin = recursive.c.origin.label('origin')
        sq = s.query(origin, count).group_by(origin).cte(recursive=False)

        # Join to full book table
        q = s.query(sq.c.count, Book) \
            .filter(Book.id == sq.c.origin) \
            .order_by(sq.c.count.desc(), Book.id)

        check_paging_orm(q=q) 

def correlate(self, *args):
        """Return a :class:`.Query` construct which will correlate the given
        FROM clauses to that of an enclosing :class:`.Query` or
        :func:`~.expression.select`.

        The method here accepts mapped classes, :func:`.aliased` constructs,
        and :func:`.mapper` constructs as arguments, which are resolved into
        expression constructs, in addition to appropriate expression
        constructs.

        The correlation arguments are ultimately passed to
        :meth:`.Select.correlate` after coercion to expression constructs.

        The correlation arguments take effect in such cases
        as when :meth:`.Query.from_self` is used, or when
        a subquery as returned by :meth:`.Query.subquery` is
        embedded in another :func:`~.expression.select` construct.

         """

        self._correlate = self._correlate.union(
            _interpret_as_from(s)
            if s is not None else None
            for s in args) 

def get_oldest_pending_db_row_with_lock(context):
    # NOTE (sai): For performance reasons, we expect this method to use baked
    # query (http://docs.sqlalchemy.org/en/latest/orm/extensions/baked.html)
    journal_dep = aliased(models.OpenDaylightJournal)
    dep_query = bakery(lambda s1: s1.query(journal_dep))
    dep_query += lambda q: q.filter(
        models.OpenDaylightJournal.seqnum == journal_dep.seqnum)
    dep_query += lambda q: q.outerjoin(
        journal_dep.depending_on, aliased=True)
    dep_query += lambda q: q.filter(
        or_(models.OpenDaylightJournal.state == odl_const.PENDING,
            models.OpenDaylightJournal.state == odl_const.PROCESSING))
    row = bakery(lambda s2: s2.query(models.OpenDaylightJournal))
    row += lambda q: q.filter(
        models.OpenDaylightJournal.state == odl_const.PENDING,
        ~ (dep_query._as_query(q.session)).exists())
    row += lambda q: q.order_by(
        asc(models.OpenDaylightJournal.last_retried))
    row = row(context.session).first()
    if row:
        update_db_row_state(context, row, odl_const.PROCESSING)

    return row 

def test_retirement_insert(session, match_lineup):
    match_from_db, lineup_from_db = match_lineup

    withdrawal = mce.Substitutions(lineup_out_id=lineup_from_db.id, time=85)
    session.add(withdrawal)

    lineup_alias = aliased(mcm.MatchLineups)
    withdrawal_from_db = session.query(mce.Substitutions)\
        .outerjoin(mcm.MatchLineups, mcm.MatchLineups.id == mce.Substitutions.lineup_in_id)\
        .join(lineup_alias, lineup_alias.id == mce.Substitutions.lineup_out_id)\
        .join(mcm.Matches).filter(mcm.Matches.id == match_from_db.id)

    assert withdrawal_from_db.count() == 1
    assert withdrawal_from_db[0].lineup_out.full_name == u"Cristiano Ronaldo"
    assert withdrawal_from_db[0].lineup_in is None
    assert withdrawal_from_db[0].time == 85 

def get_sequences_for_project(project_id, only_assigned=False):
    """
    Retrieve all sequences related to given project.
    """
    if only_assigned:
        Shot = aliased(Entity, name="shot")
        query = (
            Entity.query
            .join(Shot, Entity.id == Shot.parent_id)
            .join(Task, Shot.id == Task.entity_id)
            .filter(Entity.project_id == project_id)
            .filter(user_service.build_assignee_filter())
        )
        return fields.serialize_models(query.all())
    else:
        return entities_service.get_entities_for_project(
            project_id, get_sequence_type()["id"], "Sequence"
        ) 

def get_sequences_for_episode(episode_id, only_assigned=False):
    """
    Retrieve all sequences related to given episode.
    """
    if only_assigned:
        Shot = aliased(Entity, name="shot")
        query = (
            Entity.query
            .join(Shot, Entity.id == Shot.parent_id)
            .join(Task, Shot.id == Task.entity_id)
            .filter(Entity.parent_id == episode_id)
            .filter(user_service.build_assignee_filter())
        )
        return fields.serialize_models(query.all())
    else:
        return get_episodes({"parent_id": episode_id}) 

def process_vote(self, comment_id, username, value):
        session = Session()

        subquery = session.query(CommentVote).filter(
            CommentVote.username == username).subquery()
        vote_alias = aliased(CommentVote, subquery)
        q = session.query(Comment, vote_alias).outerjoin(vote_alias).filter(
            Comment.id == comment_id)
        comment, vote = q.one()

        if vote is None:
            vote = CommentVote(comment_id, username, value)
            comment.rating += value
        else:
            comment.rating += value - vote.value
            vote.value = value

        session.add(vote)
        session.commit()
        session.close() 

def _get_issues_by_account(self):
        acct_alias = aliased(IssueProperty)

        issues = (
            db.query(func.count(Issue.issue_id), Account.account_name)
                .join(acct_alias, Issue.issue_id == acct_alias.issue_id)
                .join(Account, acct_alias.value == Account.account_id)
                .filter(
                Account.account_type_id == aws_account_type_id,
                Account.enabled == 1,
                Issue.issue_type_id == reqtag_type_id,
                acct_alias.name == 'account_id'
            )
                .group_by(Account.account_name)
                .all()
        )

        return defaultdict(int, map(reversed, issues)) 

def rbac_filter(cls, query, mode, user):
        service_alias = aliased(models["service"])
        public_services = query.filter(models["service"].public == true())
        user_services = (
            query.join(models["service"].originals.of_type(service_alias))
            .join(models["access"], service_alias.access)
            .join(models["user"], models["access"].users)
            .filter(models["access"].services_access.contains(mode))
            .filter(models["user"].name == user.name)
        )
        user_group_services = (
            query.join(models["service"].originals.of_type(service_alias))
            .join(models["access"], service_alias.access)
            .join(models["group"], models["access"].groups)
            .join(models["user"], models["group"].users)
            .filter(models["access"].services_access.contains(mode))
            .filter(models["user"].name == user.name)
        )
        return public_services.union(user_services, user_group_services) 

def correlate(self, *args):
        """Return a :class:`.Query` construct which will correlate the given
        FROM clauses to that of an enclosing :class:`.Query` or
        :func:`~.expression.select`.

        The method here accepts mapped classes, :func:`.aliased` constructs,
        and :func:`.mapper` constructs as arguments, which are resolved into
        expression constructs, in addition to appropriate expression
        constructs.

        The correlation arguments are ultimately passed to
        :meth:`.Select.correlate` after coercion to expression constructs.

        The correlation arguments take effect in such cases
        as when :meth:`.Query.from_self` is used, or when
        a subquery as returned by :meth:`.Query.subquery` is
        embedded in another :func:`~.expression.select` construct.

         """

        self._correlate = self._correlate.union(
            _interpret_as_from(s)
            if s is not None else None
            for s in args) 

def _next_hop_ip_addresses_by_binding_filters(self,
                                                  network_id,
                                                  bgp_speaker_id):
        """Return the filters for querying nexthops by binding network"""
        address_scope = aliased(address_scope_db.AddressScope,
                                name='address_scope')
        return [models_v2.IPAllocation.port_id == l3_db.RouterPort.port_id,
            models_v2.IPAllocation.subnet_id == models_v2.Subnet.id,
            BgpSpeaker.id == bgp_speaker_id,
            BgpSpeakerNetworkBinding.bgp_speaker_id == BgpSpeaker.id,
            BgpSpeakerNetworkBinding.network_id == network_id,
            models_v2.Subnet.network_id == BgpSpeakerNetworkBinding.network_id,
            models_v2.Subnet.subnetpool_id == models_v2.SubnetPool.id,
            models_v2.SubnetPool.address_scope_id == address_scope.id,
            models_v2.Subnet.ip_version == address_scope.ip_version,
            l3_db.RouterPort.port_type == DEVICE_OWNER_ROUTER_GW] 

def outerjoin(self, *props, **kwargs):
        """Create a left outer join against this ``Query`` object's criterion
        and apply generatively, returning the newly resulting ``Query``.

        Usage is the same as the ``join()`` method.

        """
        aliased, from_joinpoint, full = kwargs.pop('aliased', False), \
            kwargs.pop('from_joinpoint', False), \
            kwargs.pop('full', False)
        if kwargs:
            raise TypeError("unknown arguments: %s" %
                            ', '.join(sorted(kwargs)))
        return self._join(props,
                          outerjoin=True, full=full, create_aliases=aliased,
                          from_joinpoint=from_joinpoint) 

def _tenant_prefixes_by_router_filters(self, router_id, bgp_speaker_id):
        binding = aliased(BgpSpeakerNetworkBinding, name='network_binding')
        subnetpool = aliased(models_v2.SubnetPool,
                             name='subnetpool')
        router_attrs = aliased(l3_attrs_db.RouterExtraAttributes,
                               name='router_attrs')
        return [models_v2.Subnet.id == models_v2.IPAllocation.subnet_id,
                models_v2.Subnet.subnetpool_id == subnetpool.id,
                l3_db.RouterPort.router_id == router_id,
                l3_db.Router.id == l3_db.RouterPort.router_id,
                l3_db.Router.id == router_attrs.router_id,
                l3_db.Router.gw_port_id == models_v2.Port.id,
                models_v2.Port.network_id == binding.network_id,
                binding.bgp_speaker_id == BgpSpeaker.id,
                l3_db.RouterPort.port_type == DEVICE_OWNER_ROUTER_INTF,
                models_v2.IPAllocation.port_id == l3_db.RouterPort.port_id] 

def query_singleton_edges_from_network(self, network: Network) -> sqlalchemy.orm.query.Query:
        """Return a query selecting all edge ids that only belong to the given network."""
        ne1 = aliased(network_edge, name='ne1')
        ne2 = aliased(network_edge, name='ne2')
        singleton_edge_ids_for_network = (
            self.session
                .query(ne1.c.edge_id)
                .outerjoin(
                    ne2, and_(
                        ne1.c.edge_id == ne2.c.edge_id,
                        ne1.c.network_id != ne2.c.network_id,
                    ),
                )
                .filter(  # noqa: E131
                    and_(
                        ne1.c.network_id == network.id,
                        ne2.c.edge_id == None,  # noqa: E711
                    ),
                )
        )
        return singleton_edge_ids_for_network 

def test_substitutions_insert(session, match_lineup, person_data):
    match_from_db, lineup_from_db = match_lineup

    bench_lineup = mcm.MatchLineups(
        match_id=match_from_db.id,
        player=mcp.Players(**person_data['generic']),
        position=mcp.Positions(name=u"Center back", type=enums.PositionType.defender),
        is_starting=False,
        is_captain=False
    )
    session.add(bench_lineup)
    session.commit()

    substitution = mce.Substitutions(
        lineup_in_id=bench_lineup.id,
        lineup_out_id=lineup_from_db.id,
        time=67
    )
    session.add(substitution)

    lineup_alias = aliased(mcm.MatchLineups)
    substitution_from_db = session.query(mce.Substitutions)\
        .join(mcm.MatchLineups, mcm.MatchLineups.id == mce.Substitutions.lineup_in_id)\
        .join(lineup_alias, lineup_alias.id == mce.Substitutions.lineup_out_id)\
        .join(mcm.Matches).filter(mcm.Matches.id == match_from_db.id)

    assert substitution_from_db.count() == 1
    assert substitution_from_db[0].lineup_out.full_name == u"Cristiano Ronaldo"
    assert substitution_from_db[0].lineup_in.full_name == u"John Doe"
    assert substitution_from_db[0].time == 67 

def _next_hop_ip_addresses_by_bgp_speaker_filters(self, bgp_speaker_id):
        """Return the filters for querying nexthops by BGP speaker"""
        router_attrs = aliased(l3_attrs_db.RouterExtraAttributes,
                               name='router_attrs')

        return [l3_db.RouterPort.port_type == DEVICE_OWNER_ROUTER_GW,
           l3_db.RouterPort.router_id == router_attrs.router_id,
           BgpSpeakerNetworkBinding.network_id == models_v2.Subnet.network_id,
           BgpSpeakerNetworkBinding.ip_version == models_v2.Subnet.ip_version,
           BgpSpeakerNetworkBinding.bgp_speaker_id == bgp_speaker_id,
           models_v2.IPAllocation.port_id == l3_db.RouterPort.port_id,
           models_v2.IPAllocation.subnet_id == models_v2.Subnet.id] 

def have_messaged_by_username_no_txn(session, username_one, username_two):
    user_one = aliased(model.User)
    user_two = aliased(model.User)
    return session.query(model.MessageThread).\
        join(user_one, user_one.id == model.MessageThread.initiator_id).\
        join(user_two, user_two.id == model.MessageThread.respondent_id).\
        filter(or_(
            and_(user_one.handle == username_one, user_two.handle == username_two),
            and_(user_one.handle == username_two, user_two.handle == username_one),
        )).exists() 

def aliased(cls, *args, **kwargs):
        """ Facility to Apply an aliased on the model::

            MyModelAliased = MyModel.aliased()

        is equal at::

            from sqlalchemy.orm import aliased
            MyModelAliased = aliased(MyModel)

        :rtype: SqlAlchemy aliased of the model
        """
        alias = aliased(cls, *args, **kwargs)
        alias.registry = alias._aliased_insp._target.registry
        return alias 

def _parse_path_and_make_aliases(entity, entity_path, attrs, aliases):
    """
    :type entity: InspectionMixin
    :type entity_path: str
    :type attrs: list
    :type aliases: OrderedDict

    Sample values:

    attrs: ['product__subject_ids', 'user_id', '-group_id',
            'user__name', 'product__name', 'product__grade_from__order']
    relations: {'product': ['subject_ids', 'name'], 'user': ['name']}

    """
    relations = {}
    # take only attributes that have magic RELATION_SPLITTER
    for attr in attrs:
        # from attr (say, 'product__grade__order')  take
        # relationship name ('product') and nested attribute ('grade__order')
        if RELATION_SPLITTER in attr:
            relation_name, nested_attr = attr.split(RELATION_SPLITTER, 1)
            if relation_name in relations:
                relations[relation_name].append(nested_attr)
            else:
                relations[relation_name] = [nested_attr]

    for relation_name, nested_attrs in relations.items():
        path = entity_path + RELATION_SPLITTER + relation_name \
               if entity_path else relation_name
        if relation_name not in entity.relations:
            raise KeyError("Incorrect path `{}`: "
                           "{} doesnt have `{}` relationship "
                           .format(path, entity, relation_name))
        relationship = getattr(entity, relation_name)
        alias = aliased(relationship.property.mapper.class_)
        aliases[path] = alias, relationship
        _parse_path_and_make_aliases(alias, path, nested_attrs, aliases) 

def _tenant_networks_by_bgp_speaker_filters(self, address_scope_ids):
        """Return the filters for querying tenant networks by BGP speaker"""
        router_attrs = aliased(l3_attrs_db.RouterExtraAttributes,
                               name='router_attrs')
        return [models_v2.IPAllocation.port_id == l3_db.RouterPort.port_id,
          l3_db.RouterPort.router_id == router_attrs.router_id,
          l3_db.RouterPort.port_type != lib_consts.DEVICE_OWNER_ROUTER_GW,
          l3_db.RouterPort.port_type != lib_consts.DEVICE_OWNER_ROUTER_SNAT,
          models_v2.IPAllocation.subnet_id == models_v2.Subnet.id,
          models_v2.Subnet.network_id != BgpSpeakerNetworkBinding.network_id,
          models_v2.Subnet.subnetpool_id == models_v2.SubnetPool.id,
          models_v2.SubnetPool.address_scope_id.in_(address_scope_ids),
          models_v2.Subnet.ip_version == BgpSpeakerNetworkBinding.ip_version,
          BgpSpeakerNetworkBinding.bgp_speaker_id == BgpSpeaker.id,
          BgpSpeaker.advertise_tenant_networks == sa.sql.true()] 

def add_entity(self, entity, alias=None):
        """add a mapped entity to the list of result columns
        to be returned."""

        if alias is not None:
            entity = aliased(entity, alias)

        self._entities = list(self._entities)
        m = _MapperEntity(self, entity)
        self._set_entity_selectables([m]) 

def add_entity(self, entity, alias=None):
        """add a mapped entity to the list of result columns
        to be returned."""

        if alias is not None:
            entity = aliased(entity, alias)

        self._entities = list(self._entities)
        m = _MapperEntity(self, entity)
        self._set_entity_selectables([m]) 

def query_index():
    q = Dataset.all_by_account(current_user, order=False)
    q = q.order_by(Dataset.updated_at.desc())

    # Filter by languages if they have been provided
    for language in request.args.getlist('languages'):
        l = aliased(DatasetLanguage)
        q = q.join(l, Dataset._languages)
        q = q.filter(l.code == language)

    # Filter by territories if they have been provided
    for territory in request.args.getlist('territories'):
        t = aliased(DatasetTerritory)
        q = q.join(t, Dataset._territories)
        q = q.filter(t.code == territory)

    # Filter by account if one has been provided
    for account in request.args.getlist('account'):
        a = aliased(Account)
        q = q.join(a, Dataset.managers)
        q = q.filter(a.name == account)

    # Return a list of languages as dicts with code, count, url and label
    languages = [{'code': code, 'count': count, 'label': LANGUAGES.get(code)}
                 for (code, count) in DatasetLanguage.dataset_counts(q)]

    territories = [{'code': code, 'count': count, 'label': COUNTRIES.get(code)}
                   for (code, count) in DatasetTerritory.dataset_counts(q)]

    pager = Pager(q, limit=15)
    return pager, languages, territories 

def _set_select_from(self, obj, set_base_alias):
        fa = []
        select_from_alias = None

        for from_obj in obj:
            info = inspect(from_obj)
            if hasattr(info, 'mapper') and \
                    (info.is_mapper or info.is_aliased_class):
                self._select_from_entity = info
                if set_base_alias and not info.is_aliased_class:
                    raise sa_exc.ArgumentError(
                        "A selectable (FromClause) instance is "
                        "expected when the base alias is being set.")
                fa.append(info.selectable)
            elif not info.is_selectable:
                raise sa_exc.ArgumentError(
                    "argument is not a mapped class, mapper, "
                    "aliased(), or FromClause instance.")
            else:
                if isinstance(from_obj, expression.SelectBase):
                    from_obj = from_obj.alias()
                if set_base_alias:
                    select_from_alias = from_obj
                fa.append(from_obj)

        self._from_obj = tuple(fa)

        if set_base_alias and \
                len(self._from_obj) == 1 and \
                isinstance(select_from_alias, expression.Alias):
            equivs = self.__all_equivs()
            self._from_obj_alias = sql_util.ColumnAdapter(
                self._from_obj[0], equivs)
        elif set_base_alias and \
                len(self._from_obj) == 1 and \
                hasattr(info, "mapper") and \
                info.is_aliased_class:
            self._from_obj_alias = info._adapter 

def correlate(self, *args):
        """Return a :class:`.Query` construct which will correlate the given
        FROM clauses to that of an enclosing :class:`.Query` or
        :func:`~.expression.select`.

        The method here accepts mapped classes, :func:`.aliased` constructs,
        and :func:`.mapper` constructs as arguments, which are resolved into
        expression constructs, in addition to appropriate expression
        constructs.

        The correlation arguments are ultimately passed to
        :meth:`.Select.correlate` after coercion to expression constructs.

        The correlation arguments take effect in such cases
        as when :meth:`.Query.from_self` is used, or when
        a subquery as returned by :meth:`.Query.subquery` is
        embedded in another :func:`~.expression.select` construct.

        """

        for s in args:
            if s is None:
                self._correlate = self._correlate.union([None])
            else:
                self._correlate = self._correlate.union(
                    sql_util.surface_selectables(_interpret_as_from(s))
                ) 

def _set_select_from(self, obj, set_base_alias):
        fa = []
        select_from_alias = None

        for from_obj in obj:
            info = inspect(from_obj)
            if hasattr(info, 'mapper') and \
                    (info.is_mapper or info.is_aliased_class):
                self._select_from_entity = info
                if set_base_alias and not info.is_aliased_class:
                    raise sa_exc.ArgumentError(
                        "A selectable (FromClause) instance is "
                        "expected when the base alias is being set.")
                fa.append(info.selectable)
            elif not info.is_selectable:
                raise sa_exc.ArgumentError(
                    "argument is not a mapped class, mapper, "
                    "aliased(), or FromClause instance.")
            else:
                if isinstance(from_obj, expression.SelectBase):
                    from_obj = from_obj.alias()
                if set_base_alias:
                    select_from_alias = from_obj
                fa.append(from_obj)

        self._from_obj = tuple(fa)

        if set_base_alias and \
                len(self._from_obj) == 1 and \
                isinstance(select_from_alias, expression.Alias):
            equivs = self.__all_equivs()
            self._from_obj_alias = sql_util.ColumnAdapter(
                self._from_obj[0], equivs)
        elif set_base_alias and \
                len(self._from_obj) == 1 and \
                hasattr(info, "mapper") and \
                info.is_aliased_class:
            self._from_obj_alias = info._adapter 

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 _get_query(self) -> 'Query':
        """
        Build, filter and sort the query.

        Returns:
            SQLAlchemy query.

        """
        subquery = graphene_sqlalchemy.get_query(self.model, self.info.context)

        request_filters = self.graphql_args.get(self.filter_arg)
        if request_filters:
            filter_set = self._get_filter_set(self.info)
            subquery = filter_set.filter(self.info, subquery, request_filters)

        sort = self.graphql_args.get('sort')
        if sort and isinstance(sort, list):
            subquery = subquery.order_by(*(col.value for col in sort))

        aliased_model = aliased(self.model, subquery.subquery())

        query = (
            graphene_sqlalchemy.get_query(self.parent_model, self.info.context)
            .join(aliased_model, self.relation)
            .options(
                contains_eager(self.relation, alias=aliased_model),
                Load(self.parent_model).load_only(*self.parent_model_pks),
            )
        )

        return query