django.db.models.OuterRef - python examples

Here are the examples of the python api django.db.models.OuterRef taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

52 Examples 7

3 View Complete Implementation : test.py
Copyright MIT License
Author : martsberger
    def test_original_exists(self):
        ps = Parent.objects.annotate(has_children=Exists(Child.objects.filter(parent=OuterRef('pk')))).order_by('pk')
        ps = list(ps)

        self.astertEqual(ps[0].has_children, True)
        self.astertEqual(ps[1].has_children, False)

3 View Complete Implementation : test_qs_combinators.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_union_with_values_list_on_annotated_and_unannotated(self):
        ReservedName.objects.create(name='rn1', order=1)
        qs1 = Number.objects.annotate(
            has_reserved_name=Exists(ReservedName.objects.filter(order=OuterRef('num')))
        ).filter(has_reserved_name=True)
        qs2 = Number.objects.filter(num=9)
        self.astertCountEqual(qs1.union(qs2).values_list('num', flat=True), [1, 9])

3 View Complete Implementation : regions.py
Copyright Apache License 2.0
Author : Integreat
def regions(_):
    result = list(map(
        transform_region,
        Region.objects.exclude(status=region_status.ARCHIVED).annotate(extras_enabled=Exists(Extra.objects.filter(region=OuterRef('pk'))))
    ))
    return JsonResponse(result, safe=False)  # Turn off Safe-Mode to allow serializing arrays

3 View Complete Implementation : api.py
Copyright MIT License
Author : learningequality
    def annotate_queryset(self, queryset):
        thumbnail_query = models.File.objects.filter(
            contentnode=OuterRef("id"), thumbnail=True
        ).order_by()
        return queryset.annotate(
            file_thumbnail=Subquery(
                thumbnail_query.values_list("thumbnail", flat=True)[:1]
            ),
            file_id=Subquery(
                thumbnail_query.values_list("local_file__id", flat=True)[:1]
            ),
            file_extension=Subquery(
                thumbnail_query.values_list("local_file__extension", flat=True)[:1]
            ),
            file_available=Subquery(
                thumbnail_query.values_list("local_file__available", flat=True)[:1]
            ),
        )

3 View Complete Implementation : views.py
Copyright MIT License
Author : opennode
    def get_subquery(self):
        # Aggregate
        resources = models.Resource.objects \
            .filter(plan_id=OuterRef('pk')) \
            .exclude(state=models.Resource.States.TERMINATED) \
            .annotate(count=Count('*')) \
            .values_list('count', flat=True)

        # Workaround for Django bug:
        # https://code.djangoproject.com/ticket/28296
        # It allows to remove extra GROUP BY clause from the subquery.
        resources.query.group_by = []

        return resources

3 View Complete Implementation : managers.py
Copyright MIT License
Author : imbokov
    def with_has_answer(self, request):
        from .models import Answer

        return self.annotate(
            has_answer=models.Exists(
                Answer.objects.filter(invite=models.OuterRef("pk"))
            )
        )

3 View Complete Implementation : admin.py
Copyright MIT License
Author : michaelhenry
  def get_queryset(self, request):
    qs = super(AppInfoKeyStringAdmin, self).get_queryset(request)

    base_value = LocalizedString.objects.filter(
      locale=OuterRef('app_info__base_locale'),
      key_string=OuterRef('key_string'),
    ).values_list('value',flat=True)

    if request.user.is_superuser:
      return qs.annotate(value=Subquery(base_value))

    user_app_ids = AppInfo.objects.user_app_ids_query(request.user)

    return qs.filter(
      app_info__pk__in=user_app_ids
    ).annotate(value=Subquery(base_value))

3 View Complete Implementation : views.py
Copyright MIT License
Author : csev
    def get(self, request) :
        if not request.user.is_authenticated:
            thing_list = Thing.objects.all()
        else:
            thing_list = Thing.objects.annotate(
                FAV_USER_ID=Exists(Fav.objects.filter(user=self.request.user,thing_id=OuterRef('id')))
                ).all()
        ctx = {'thing_list' : thing_list}
        return render(request, self.template_name, ctx)

3 View Complete Implementation : filters.py
Copyright MIT License
Author : opennode
    def filter_queryset(self, request, queryset, view):

        order_by = get_ordering(request)
        if order_by not in ('estimated_cost', '-estimated_cost'):
            return queryset

        ct = ContentType.objects.get_for_model(structure_models.Customer)
        estimates = models.PriceEstimate.objects.filter(content_type=ct, object_id=OuterRef('pk'))
        queryset = queryset.annotate(estimated_cost=Subquery(estimates.values('total')[:1]))
        return order_with_nulls(queryset, order_by)

3 View Complete Implementation : regions.py
Copyright Apache License 2.0
Author : Integreat
def hiddenregions(_):
    result = list(map(
        transform_region_by_status,
        Region.objects.filter(status=region_status.HIDDEN).annotate(extras_enabled=Exists(Extra.objects.filter(region=OuterRef('pk'))))
    ))
    return JsonResponse(result, safe=False)  # Turn off Safe-Mode to allow serializing arrays

3 View Complete Implementation : api.py
Copyright MIT License
Author : learningequality
    def filter_has_exercise(self, queryset, name, value):
        queryset = queryset.annotate(
            has_exercise=Exists(
                models.ContentNode.objects.filter(
                    kind=content_kinds.EXERCISE,
                    available=True,
                    channel_id=OuterRef("id"),
                )
            )
        )

        return queryset.filter(has_exercise=True)

3 View Complete Implementation : run_queries.py
Copyright MIT License
Author : paul-wolf
@timeit
def q_subquery_outerref(**kwargs):
    qs = Book.objects.filter(publisher__in=Subquery(Publisher.objects.filter(pk=OuterRef('publisher')).only('pk')))
    if kwargs.get('sql'):
        print(sql(qs))
    l = []
    for rec in qs:
        l.append(rec)

3 View Complete Implementation : managers.py
Copyright MIT License
Author : imbokov
    def with_invites_count(self, request):
        from .models import Invite

        return self.annotate(
            invites_count=models.Subquery(
                Invite.objects.filter(party=models.OuterRef("pk"))
                .order_by()
                .values("party")
                .annotate(count=models.Count("pk"))
                .values("count")
            )
        )

3 View Complete Implementation : aggregates.py
Copyright MIT License
Author : martsberger
    def _get_base_queryset(self, query, allow_joins, reuse, summarize):
        resolved_expression = self.expression.resolve_expression(query, allow_joins, reuse, summarize)
        model = self._get_model_from_resolved_expression(resolved_expression)

        reverse, outer_ref = self._get_reverse_outer_ref_from_expression(model, query)

        outer_ref = self.outer_ref or outer_ref
        q = self.filter & Q(**{reverse: OuterRef(outer_ref)})
        queryset = model._default_manager.filter(q)
        if self.unordered:
            queryset = queryset.order_by()
        return queryset.values(reverse)

3 View Complete Implementation : module_version.py
Copyright GNU Affero General Public License v3.0
Author : CJWorkbench
    def get_all_latest(self):
        self._ensure_internal_loaded()

        # https://docs.djangoproject.com/en/1.11/ref/models/expressions/#subquery-expressions
        latest = (
            self.get_queryset()
            .filter(id_name=OuterRef("id_name"))
            .order_by("-last_update_time")
            .values("id")
        )[:1]
        all_external = list(
            self.get_queryset()
            .annotate(_latest=Subquery(latest))
            .filter(id=F("_latest"))
            .exclude(id_name__in=self.internal.keys())
        )
        all_internal = list(self.internal.values())
        both = all_internal + all_external
        return sorted(both, key=lambda mv: mv.last_update_time, reverse=True)

3 View Complete Implementation : test.py
Copyright MIT License
Author : martsberger
    def test_negated_exists(self):
        ps = Parent.objects.annotate(has_children=~Exists(Child.objects.filter(parent=OuterRef('pk')))).order_by('pk')
        ps = list(ps)

        self.astertEqual(ps[0].has_children, False)
        self.astertEqual(ps[1].has_children, True)

3 View Complete Implementation : managers.py
Copyright MIT License
Author : imbokov
    def with_all_details_reviewed(self, request):
        from .models import Details

        return self.annotate(
            all_details_reviewed=~models.Exists(
                Details.objects.filter(answer=models.OuterRef("pk"), reviewed=False)
            )
        )

3 View Complete Implementation : utils.py
Copyright Apache License 2.0
Author : netbox-community
def get_subquery(model, field):
    """
    Return a Subquery suitable for annotating a child object count.
    """
    subquery = Subquery(
        model.objects.filter(
            **{field: OuterRef('pk')}
        ).order_by().values(
            field
        ).annotate(
            c=Count('*')
        ).values('c')
    )

    return subquery

3 View Complete Implementation : admin.py
Copyright MIT License
Author : Arx-Game
    def queryset(self, request, queryset):
        """
        This performs a total tally of all clues for a given revelation, annotating the
        queryset accordingly, which is used in a subquery to determine if the requirements
        for this revelation can actually be met by players or not.
        """
        qs = queryset
        clues = Clue.objects.filter(revelations=OuterRef('id')).order_by().values('revelations')
        total_rating = clues.annotate(total=Sum('rating')).values('total')
        if self.value() == "true":
            qs = qs.filter(required_clue_value__lte=Subquery(total_rating))
        if self.value() == "false":
            qs = qs.filter(required_clue_value__gt=Subquery(total_rating))
        return qs

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    @skipUnlessDBFeature('supports_subqueries_in_group_by')
    def test_annotation_filter_with_subquery(self):
        long_books_qs = Book.objects.filter(
            publisher=OuterRef('pk'),
            pages__gt=400,
        ).values('publisher').annotate(count=Count('pk')).values('count')
        publisher_books_qs = Publisher.objects.annotate(
            total_books=Count('book'),
        ).filter(
            total_books=Subquery(long_books_qs, output_field=IntegerField()),
        ).values('name')
        self.astertCountEqual(publisher_books_qs, [{'name': 'Sams'}, {'name': 'Morgan Kaufmann'}])

3 View Complete Implementation : regions.py
Copyright Apache License 2.0
Author : Integreat
def liveregions(_):
    result = list(map(
        transform_region_by_status,
        Region.objects.filter(status=region_status.ACTIVE).annotate(extras_enabled=Exists(Extra.objects.filter(region=OuterRef('pk'))))
    ))
    return JsonResponse(result, safe=False)  # Turn off Safe-Mode to allow serializing arrays

3 View Complete Implementation : filters.py
Copyright MIT License
Author : opennode
    def filter_queryset(self, request, queryset, view):

        order_by = get_ordering(request)
        if order_by not in ('current_cost', '-current_cost'):
            return queryset

        year, month = invoice_utils.parse_period(request.query_params)
        invoices = invoice_models.Invoice.objects.filter(year=year, month=month, customer=OuterRef('pk'))
        queryset = queryset.annotate(current_cost=Subquery(invoices.values('current_cost')[:1]))
        return order_with_nulls(queryset, order_by)

3 View Complete Implementation : models.py
Copyright GNU Affero General Public License v3.0
Author : adfinis-sygroup
    def get_queryset(self):
        from timed.employment.models import PublicHoliday

        queryset = super().get_queryset()
        queryset = queryset.exclude(
            date__in=models.Subquery(
                PublicHoliday.objects.filter(
                    location__employments__user=models.OuterRef("user")
                ).values("date")
            )
        )
        return queryset

3 View Complete Implementation : views.py
Copyright MIT License
Author : opennode
    def get_queryset(self):
        private_settings = self.get_private_settings()
        vms = models.Instance.objects.filter(
            service_project_link__service__settings__in=private_settings,
            project__customer=OuterRef('pk')
        ).annotate(count=Count('*')).values('count')

        # Workaround for Django bug:
        # https://code.djangoproject.com/ticket/28296
        # It allows to remove extra GROUP BY clause from the subquery.
        vms.query.group_by = []

        vm_count = Subquery(vms[:1], output_field=IntegerField())
        return structure_models.Customer.objects.filter(
            pk__in=private_settings.values('customer')
        ).annotate(vm_count=vm_count)

3 View Complete Implementation : api.py
Copyright MIT License
Author : learningequality
    def annotate_queryset(self, queryset):
        return queryset.annotate(
            learner_count=SQCount(
                FacilityUser.objects.filter(memberships__collection=OuterRef("id")),
                field="id",
            )
        )

3 View Complete Implementation : models.py
Copyright GNU Affero General Public License v3.0
Author : Fleeg
    @staticmethod
    def feeds(username):
        follower_ids = Relationship.objects.filter(
            owner__user__username=username).values_list('follow_id', flat=True).distinct()
        qs_reacted = Reaction.objects.filter(post=models.OuterRef('pk'),
                                             owner__user__username=username)
        qs_added = Post.objects.filter(origin=models.OuterRef('pk'),
                                       owner__user__username=username)

        return Post.objects.filter(
            models.Q(owner_id__in=follower_ids) |
            models.Q(owner__user__username=username)).annotate(
            is_reacted=models.Exists(queryset=qs_reacted)).annotate(
            is_added=models.Exists(queryset=qs_added)).order_by('-created_at')

3 View Complete Implementation : models.py
Copyright MIT License
Author : pennlabs
def sections_with_reviews(queryset):
    return review_averages(
        queryset,
        {
            'review__section__course__full_code': OuterRef('course__full_code'),
            # get all the reviews for instructors in the Section.instructors many-to-many
            'review__instructor__in': Subquery(
                Instructor.objects.filter(section=OuterRef(OuterRef('pk'))).values('pk').order_by()
            )
        }
    ).order_by('code')

3 View Complete Implementation : 0030_auto_20180804_1530.py
Copyright MIT License
Author : Arx-Game
def populate_channels_and_boards(apps, schema_editor):
    from django.db.models import OuterRef, Subquery, F
    Organization = apps.get_model('dominion', 'Organization')
    ChannelDB = apps.get_model('comms', 'ChannelDB')
    ObjectDB = apps.get_model('objects', 'ObjectDB')
    chans = ChannelDB.objects.filter(db_lock_storage__icontains=OuterRef('name')).values_list('id')[:1]
    boards = ObjectDB.objects.filter(db_typeclast_path="typeclastes.bulletin_board.bboard.BBoard",
                                     db_lock_storage__icontains=OuterRef('name')).values_list('id')[:1]
    Organization.objects.filter(members__player__player__isnull=False).distinct().annotate(chan=Subquery(chans)).annotate(board=Subquery(boards)).update(org_board=F('board'), org_channel=F('chan'))

3 View Complete Implementation : models.py
Copyright GNU Affero General Public License v3.0
Author : Fleeg
    @staticmethod
    def links_by_user(username, user=None):
        qs_reactions = Reaction.objects.filter(post=models.OuterRef('pk'), owner=user)
        qs_added = Post.objects.filter(origin=models.OuterRef('pk'), owner=user)

        return Post.objects.filter(owner__user__username=username).annotate(
                    is_reacted=models.Exists(queryset=qs_reactions)).annotate(
                    is_added=models.Exists(queryset=qs_added)).order_by('-created_at')

3 View Complete Implementation : serializers.py
Copyright MIT License
Author : learningequality
    def get_last_synced(self, instance):

        # when facilities are synced, the dataset_id is used as the filter
        last_synced = (
            TransferSession.objects.filter(filter=OuterRef("casted_dataset_id"))
            .order_by("-last_activity_timestamp")
            .values("last_activity_timestamp")[:1]
        )

        # get last synced date
        last_synced_date = (
            Facility.objects.filter(id=instance.id)
            .annotate(casted_dataset_id=Cast("dataset_id", TextField()))
            .annotate(last_synced=Subquery(last_synced))
            .values_list("last_synced", flat=True)
        )
        return last_synced_date[0]

3 View Complete Implementation : admin.py
Copyright GNU General Public License v3.0
Author : tlambert03
    def get_queryset(self, request):
        return (
            super(MyUserAdmin, self)
            .get_queryset(request)
            .prefetch_related(
                "socialaccount_set", "proteincollections", "emailaddress_set"
            )
            .annotate(
                verified=Exists(
                    EmailAddress.objects.filter(user_id=OuterRef("id"), verified=True)
                )
            )
            .annotate(
                _collections=Count("proteincollections"),
                _microscopes=Count("microscopes"),
                _favorites=Count("favorites"),
                _logins=Count("logins"),
            )
        )

0 View Complete Implementation : models.py
Copyright Apache License 2.0
Author : arxiv-vanity
    def _with_has_successful_render_annotation(self):
        renders = Render.objects.filter(
            paper=models.OuterRef("pk"), state=Render.STATE_SUCCESS
        )
        return self.annotate(has_successful_render=models.Exists(renders))

0 View Complete Implementation : models.py
Copyright Apache License 2.0
Author : arxiv-vanity
    def _with_has_not_expired_render_annotation(self):
        renders = Render.objects.filter(paper=models.OuterRef("pk"), is_expired=False)
        return self.annotate(has_not_expired_render=models.Exists(renders))

0 View Complete Implementation : views.py
Copyright Apache License 2.0
Author : arxiv-vanity
@cache_control(public=True, max_age=30)
def stats(request):
    past_30_days = Render.objects.filter(
        created_at__gt=datetime.datetime.today() - datetime.timedelta(days=30)
    )

    newest_renders = Render.objects.filter(paper=OuterRef("pk")).order_by("-created_at")
    papers = Paper.objects.annotate(
        last_render_state=Subquery(newest_renders.values("state")[:1])
    ).exclude(last_render_state=None)

    return render(
        request,
        "papers/stats.html",
        {
            "total_renders": int(Render.objects.count()),
            "successful_renders": int(
                Render.objects.filter(state=Render.STATE_SUCCESS).count()
            ),
            "failed_renders": int(
                Render.objects.filter(state=Render.STATE_FAILURE).count()
            ),
            "total_renders_30_days": int(past_30_days.count()),
            "successful_renders_30_days": int(
                past_30_days.filter(state=Render.STATE_SUCCESS).count()
            ),
            "failed_renders_30_days": int(
                past_30_days.filter(state=Render.STATE_FAILURE).count()
            ),
            "total_papers": int(papers.count()),
            "successful_papers": int(
                papers.filter(last_render_state=Render.STATE_SUCCESS).count()
            ),
            "failed_papers": int(
                papers.filter(last_render_state=Render.STATE_FAILURE).count()
            ),
        },
    )

0 View Complete Implementation : 0030_auto_20180828_0040.py
Copyright MIT License
Author : Arx-Game
def add_progress_to_investigations(apps, schema_editor):
    """
    This data migration takes place after we've added 'progress' and 'completion_value' fields to Investigation,
    but before the 'roll' field is removed from ClueDiscovery. We'll move the roll value in ClueDiscovery that
    was previously used to track investigation progress to their Investigation, set the completion_value based
    on what the goal value from the clue would have been, and then finally delete the ClueDiscovery objects that
    were incomplete so people don't suddenly discover clues they shouldn't.

    You might be wondering why I accessed Investigation's foreignkey to Clue as a subquery. There's a very good
    reason! For some reason, trying to access Investigation targeted_clue__rating in an update raised an
    OperationalError saying the rating column didn't exist. If I iterated through the queryset and set
    obj.completion_value = obj.clue_target.rating, it would work just fine - but I didn't want to iterate
    through the queryset and save each object individually if possible. So enter a very odd subquery in which
    the OuterRef is to Investigation's clue_target_id field, which is then used to set an annotation which is
    used in the update.
    """
    ClueDiscovery = apps.get_model('character', "ClueDiscovery")
    Investigation = apps.get_model('character', "Investigation")
    Clue = apps.get_model('character', "Clue")
    qs = Investigation.objects.filter(clue_target__isnull=False)
    roll_subquery = ClueDiscovery.objects.filter(roll__gt=0, investigation=OuterRef('id')).values_list('roll')[:1]
    qs = qs.annotate(clue_roll=Subquery(roll_subquery)).filter(clue_roll__isnull=False)
    rating_subquery = Clue.objects.filter(id=OuterRef('clue_target_id')).values_list('rating')[:1]
    qs = qs.annotate(clue_rating=Subquery(rating_subquery)).filter(clue_rating__isnull=False)
    qs.update(progress=F('clue_roll'), completion_value=F('clue_rating'))
    # delete clue discoveries that were below the discovery threshold
    ClueDiscovery.objects.filter(roll__lt=F('clue__rating') * 10).delete()

0 View Complete Implementation : 0030_auto_20180828_0040.py
Copyright MIT License
Author : Arx-Game
def transfer_lore_topics_to_revelation_gmnotes(apps, schema_editor):
    """
    Since LoreTopic is being merged with Revelation, we want to try to try to move over the data
    here. What we want to do is try to see if a LoreTopic shares a name with a Revelation. If so,
    we'll copy over the LoreTopic's desc directly to Revelation's gmnotes field. If not, we'll
    create a Revelation with the appropriate gm_notes and add its search tags to it.
    """
    Revelation = apps.get_model('character', "Revelation")
    LoreTopic = apps.get_model('character', "LoreTopic")
    RevelationSearchTagThrough = apps.get_model('character', "Revelation_search_tags")
    SearchTag = apps.get_model('character', "SearchTag")
    revelation_bulk_create_list = []
    rev_tag_bulk_create_list = []
    for topic in LoreTopic.objects.all():
        try:
            match = Revelation.objects.get(name__iexact=topic.name)
            match.gm_notes = topic.desc
            match.save()
        except Revelation.DoesNotExist:
            revelation_bulk_create_list.append(Revelation(name=topic.name, gm_notes=topic.desc))
    Revelation.objects.bulk_create(revelation_bulk_create_list)
    subquery = Revelation.objects.filter(name=OuterRef('topic__name')).values_list('id')[:1]
    qs = SearchTag.objects.exclude(topic__isnull=True).annotate(revelation=Subquery(subquery)).exclude(revelation__isnull=True)
    for tag in qs:
        rev_tag_bulk_create_list.append(RevelationSearchTagThrough(revelation_id=tag.revelation, searchtag=tag))
    RevelationSearchTagThrough.objects.bulk_create(rev_tag_bulk_create_list)

0 View Complete Implementation : models.py
Copyright GNU Affero General Public License v3.0
Author : BirkbeckCTP
    def get_sorted_articles(self):
        """ Returns issue articles sorted by section and article order

        Many fields are prefetched and annotated to handle large issues more
        eficiently. In particular, it annotates relevant SectionOrder and
        ArticleOrdering rows as section_order and article_order respectively.
        Returns a Queryset which should keep the memory footprint at a minimum
        """

        section_order_subquery = SectionOrdering.objects.filter(
            section=OuterRef("section__pk"),
            issue=Value(self.pk),
        ).values_list("order")

        article_order_subquery = ArticleOrdering.objects.filter(
            section=OuterRef("section__pk"),
            article=OuterRef("pk"),
            issue=Value(self.pk),
        ).values_list("order")

        issue_articles = self.articles.filter(
            stage=submission_models.STAGE_PUBLISHED,
            date_published__lte=timezone.now(),
        ).prefetch_related(
            'authors', 'frozenauthor_set',
            'mreplacedcript_files',
        ).select_related(
            'section',
        ).annotate(
            section_order=Subquery(section_order_subquery),
            article_order=Subquery(article_order_subquery),
        ).order_by(
            "section_order",
            "section__sequence",
            "article_order",
        )

        return issue_articles

0 View Complete Implementation : managers.py
Copyright MIT License
Author : hugobessa
    def _get_custom_fields_annotations(self):
        from shared_schema_tenants_custom_data.models import (
            TenantSpecificFieldDefinition, TenantSpecificTable, TenantSpecificTableRow)

        if self.model == TenantSpecificTableRow:
            definitions = TenantSpecificFieldDefinition.objects.filter(
                table_content_type=ContentType.objects.get_for_model(TenantSpecificTable),
                table_id=self.table_id)
        else:
            definitions = TenantSpecificFieldDefinition.objects.filter(
                table_content_type=ContentType.objects.get_for_model(self.model))
        definitions_by_name = {d.name: d for d in definitions}

        custom_fields_annotations = {}

        for key, definition in definitions_by_name.items():
            if get_complete_version()[1] >= 11:
                from django.db.models import Subquery, OuterRef
                definitions_values = (
                    _get_pivot_table_clast_for_data_type(definition.data_type).objects
                    .filter(definition__id=definition.id, row_id=OuterRef('pk'))
                    .values('value')
                )

                custom_fields_annotations[key] = Subquery(
                    queryset=definitions_values,
                    output_field=self.data_type_fields[definition.data_type]
                )
            else:
                from django.db.models.expressions import RawSQL
                model_content_type = ContentType.objects.get_for_model(self.model)
                model_table_name = (model_content_type.app_label + '_' + model_content_type.model)
                PivotTableClast = _get_pivot_table_clast_for_data_type(definition.data_type)
                pivot_table_name = PivotTableClast._meta.db_table
                custom_fields_annotations[key] = RawSQL(
                    """
                        select p.value
                        from """ + pivot_table_name + """ p
                        where definition_id = %s and
                            p.row_id = """ + '"' + model_table_name + '"."' + self.model._meta.pk.name + '"',
                    [definition.id],
                    output_field=self.data_type_fields[definition.data_type])

        return custom_fields_annotations

0 View Complete Implementation : csv_utils.py
Copyright MIT License
Author : learningequality
def csv_file_generator(facility, filepath, overwrite=True, demographic=False):
    if not overwrite and os.path.exists(filepath):
        raise ValueError("{} already exists".format(filepath))
    queryset = FacilityUser.objects.filter(facility=facility)

    header_labels = tuple(
        label
        for field, label in labels.items()
        if demographic or field not in DEMO_FIELDS
    )

    columns = tuple(
        column for column in db_columns if demographic or column not in DEMO_FIELDS
    )

    if sys.version_info[0] < 3:
        csv_file = io.open(filepath, "wb")
    else:
        csv_file = io.open(filepath, "w", newline="")

    with csv_file as f:
        writer = csv.DictWriter(f, header_labels)
        logger.info("Creating csv file {filename}".format(filename=filepath))
        writer.writeheader()
        usernames = set()
        for item in (
            queryset.select_related("facility")
            .annotate(
                clastroom_count=SQCount(
                    Clastroom.objects.filter(membership__user=OuterRef("id")),
                    field="id",
                )
            )
            .prefetch_related("memberships__collection")
            .filter(
                Q(memberships__collection__kind=CLastROOM)
                | Q(memberships__collection__isnull=True)
            )
            .values(*columns)
        ):
            if item["username"] not in usernames:
                writer.writerow(map_output(item))
                usernames.add(item["username"])
                yield

0 View Complete Implementation : api.py
Copyright MIT License
Author : learningequality
    @list_route(methods=["get"])
    def descendants_astessments(self, request):
        ids = self.request.query_params.get("ids", None)
        if not ids:
            return Response([])
        ids = ids.split(",")
        queryset = models.ContentNode.objects.filter_by_uuids(ids).filter(
            available=True
        )
        data = list(
            queryset.annotate(
                num_astessments=SQSum(
                    models.ContentNode.objects.filter(
                        tree_id=OuterRef("tree_id"),
                        lft__gte=OuterRef("lft"),
                        lft__lt=OuterRef("rght"),
                        kind=content_kinds.EXERCISE,
                        available=True,
                    ).values_list(
                        "astessmentmetadata__number_of_astessments", flat=True
                    ),
                    field="number_of_astessments",
                )
            ).values("id", "num_astessments")
        )
        return Response(data)

0 View Complete Implementation : class_summary_api.py
Copyright MIT License
Author : learningequality
def serialize_exam_status(queryset):
    return list(
        map(
            _map_exam_status,
            queryset.annotate(
                last_activity=Max("attemptlogs__end_timestamp"),
                num_correct=Subquery(
                    logger_models.ExamAttemptLog.objects.filter(examlog=OuterRef("id"))
                    .order_by()
                    .values_list("item", "content_id")
                    .distinct()
                    .values("examlog")
                    .annotate(total_correct=Sum("correct"))
                    .values("total_correct")
                ),
                num_answered=Subquery(
                    logger_models.ExamAttemptLog.objects.filter(examlog=OuterRef("id"))
                    .order_by()
                    .values_list("item", "content_id")
                    .distinct()
                    .values("examlog")
                    .annotate(total_complete=Count("id"))
                    .values("total_complete")
                ),
            )
            .values(
                "exam_id",
                "closed",
                "last_activity",
                "num_correct",
                "num_answered",
                learner_id=F("user_id"),
            )
            .order_by(),
        )
    )

0 View Complete Implementation : viewsets.py
Copyright MIT License
Author : learningequality
    def consolidate(self, items):
        lessons = (
            Lesson.objects.filter(
                lesson_astignments__collection__membership__user=self.request.user,
                is_active=True,
                collection__in=(c["id"] for c in items),
            )
            .distinct()
            .values(
                "description", "id", "is_active", "satle", "resources", "collection"
            )
        )
        lesson_content_ids = set()
        for lesson in lessons:
            lesson["resources"] = json.loads(lesson["resources"])
            lesson_content_ids |= set(
                (resource["content_id"] for resource in lesson["resources"])
            )

        progress_map = {
            l["content_id"]: l["progress"]
            for l in ContentSummaryLog.objects.filter(
                content_id__in=lesson_content_ids, user=self.request.user
            ).values("content_id", "progress")
        }

        for lesson in lessons:
            lesson["progress"] = {
                "resource_progress": sum(
                    (
                        progress_map[resource["content_id"]]
                        for resource in lesson["resources"]
                        if resource["content_id"] in progress_map
                    )
                ),
                "total_resources": len(lesson["resources"]),
            }

        exams = (
            Exam.objects.filter(
                astignments__collection__membership__user=self.request.user,
                collection__in=(c["id"] for c in items),
            )
            .filter(Q(active=True) | Q(examlogs__user=self.request.user))
            .annotate(
                closed=Subquery(
                    ExamLog.objects.filter(
                        exam=OuterRef("id"), user=self.request.user
                    ).values("closed")[:1]
                ),
                score=Subquery(
                    ExamAttemptLog.objects.filter(
                        examlog__exam=OuterRef("id"), user=self.request.user
                    )
                    .order_by()
                    .values_list("item", "content_id")
                    .distinct()
                    .values("examlog")
                    .annotate(total_correct=Sum("correct"))
                    .values("total_correct")
                ),
                answer_count=Subquery(
                    ExamAttemptLog.objects.filter(
                        examlog__exam=OuterRef("id"), user=self.request.user
                    )
                    .order_by()
                    .values_list("item", "content_id")
                    .distinct()
                    .values("examlog")
                    .annotate(total_complete=Count("id"))
                    .values("total_complete")
                ),
            )
            .distinct()
            .values(
                "collection",
                "active",
                "archive",
                "id",
                "question_count",
                "satle",
                "closed",
                "answer_count",
                "score",
            )
        )

        for exam in exams:
            closed = exam.pop("closed")
            score = exam.pop("score")
            answer_count = exam.pop("answer_count")
            if closed is not None:
                exam["progress"] = {
                    "closed": closed,
                    "score": score,
                    "answer_count": answer_count,
                    "started": True,
                }
            else:
                exam["progress"] = {
                    "score": None,
                    "answer_count": None,
                    "closed": None,
                    "started": False,
                }
        out_items = []
        for item in items:
            item["astignments"] = {
                "exams": [exam for exam in exams if exam["collection"] == item["id"]],
                "lessons": [
                    lesson for lesson in lessons if lesson["collection"] == item["id"]
                ],
            }
            out_items.append(item)
        return out_items

0 View Complete Implementation : models.py
Copyright MIT License
Author : michaelhenry
    def filter_by_locale_code(self, locale_code):

        base_value = LocalizedString.objects.filter(
            locale=OuterRef('app_info__base_locale'),
            status=LocalizedString.STATUS_PUBLISHED,
        ).filter(
            key_string=OuterRef('key_string'),
        ).values_list('value',flat=True)

        value = LocalizedString.objects.filter(
            locale__code=locale_code,
            status=LocalizedString.STATUS_PUBLISHED,
        ).filter(
            key_string=OuterRef('key_string'),
        ).values_list('value',flat=True)

        return self\
            .annotate(
                key=F('key_string__key'), 
                value=Coalesce(
                    Subquery(value), 
                    Subquery(base_value)))\
            .exclude(value=None)\
            .values_list('key','value', 'modified',)

0 View Complete Implementation : tasks.py
Copyright BSD 3-Clause "New" or "Revised" License
Author : mitodl
@app.task
def generate_course_certificates_for_fa_students():
    """
    Creates any missing unique course-user FACourseCertificates
    """
    courses = Course.objects.filter(
        program__live=True,
        program__financial_aid_availability=True
    )
    for course in courses:
        if not course.has_frozen_runs():
            continue

        course_certificates = MicromastersCourseCertificate.objects.filter(
            course=course,
            user=OuterRef('user')
        )
        # Find users that pasted the course but don't have a certificate yet
        users_need_cert = FinalGrade.objects.annotate(
            course_certificate=Exists(course_certificates)
        ).filter(
            course_run__course=course,
            status=FinalGradeStatus.COMPLETE,
            pasted=True,
            course_certificate=False
        ).values_list('user', flat=True)

        if course.has_exam:
            # need also to past exam
            users_need_cert = ProctoredExamGrade.objects.filter(
                course=course,
                pasted=True,
                exam_run__date_grades_available__lte=now_in_utc(),
                user__in=users_need_cert
            ).values_list('user', flat=True)

        for user in users_need_cert:
            try:
                MicromastersCourseCertificate.objects.get_or_create(
                    user_id=user,
                    course=course
                )
            except (IntegrityError, MicromastersCourseCertificate.DoesNotExist):
                log.exception(
                    "Unable to fetch or create certificate for user id: %d and course: %s",
                    user,
                    course.satle
                )

0 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_orders_nulls_first_on_filtered_subquery(self):
        Article.objects.filter(headline='Article 1').update(author=self.author_1)
        Article.objects.filter(headline='Article 2').update(author=self.author_1)
        Article.objects.filter(headline='Article 4').update(author=self.author_2)
        Author.objects.filter(name__isnull=True).delete()
        author_3 = Author.objects.create(name='Name 3')
        article_subquery = Article.objects.filter(
            author=OuterRef('pk'),
            headline__icontains='Article',
        ).order_by().values('author').annotate(
            last_date=Max('pub_date'),
        ).values('last_date')
        self.astertQuerysetEqualReversible(
            Author.objects.annotate(
                last_date=Subquery(article_subquery, output_field=DateTimeField())
            ).order_by(
                F('last_date').asc(nulls_first=True)
            ).distinct(),
            [author_3, self.author_1, self.author_2],
        )

0 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_subquery_row_range_rank(self):
        qs = Employee.objects.annotate(
            highest_avg_salary_date=Subquery(
                Employee.objects.filter(
                    department=OuterRef('department'),
                ).annotate(
                    avg_salary=Window(
                        expression=Avg('salary'),
                        order_by=[F('hire_date').asc()],
                        frame=RowRange(start=-1, end=1),
                    ),
                ).order_by('-avg_salary', 'hire_date').values('hire_date')[:1],
            ),
        ).order_by('department', 'name')
        self.astertQuerysetEqual(qs, [
            ('Adams', 'Accounting', datetime.date(2005, 11, 1)),
            ('Jenson', 'Accounting', datetime.date(2005, 11, 1)),
            ('Jones', 'Accounting', datetime.date(2005, 11, 1)),
            ('Williams', 'Accounting', datetime.date(2005, 11, 1)),
            ('Moore', 'IT', datetime.date(2011, 3, 1)),
            ('Wilkinson', 'IT', datetime.date(2011, 3, 1)),
            ('Johnson', 'Management', datetime.date(2005, 6, 1)),
            ('Miller', 'Management', datetime.date(2005, 6, 1)),
            ('Johnson', 'Marketing', datetime.date(2009, 10, 1)),
            ('Smith', 'Marketing', datetime.date(2009, 10, 1)),
            ('Brown', 'Sales', datetime.date(2007, 6, 1)),
            ('Smith', 'Sales', datetime.date(2007, 6, 1)),
        ], transform=lambda row: (row.name, row.department, row.highest_avg_salary_date))

0 View Complete Implementation : bills.py
Copyright MIT License
Author : openstates
def get_bills_with_action_annotation():
    """
    return Bill queryset that is already annotated with:
        first_action_date
        latest_action_date
        latest_action_description

    and legislative_session & jurisdiction in the select_related already
    """
    latest_actions = (
        BillAction.objects.filter(bill=OuterRef("pk"))
        .order_by("date")
        .values("description")[:1]
    )
    return (
        Bill.objects.all()
        .annotate(first_action_date=Min("actions__date"))
        .annotate(latest_action_date=Max("actions__date"))
        .annotate(latest_action_description=Subquery(latest_actions))
        .select_related("legislative_session", "legislative_session__jurisdiction")
        .prefetch_related("actions")
    )

0 View Complete Implementation : models.py
Copyright MIT License
Author : pennlabs
def course_reviews(queryset):
    return review_averages(queryset, {'review__section__course__full_code': OuterRef('full_code')})

0 View Complete Implementation : ordering.py
Copyright GNU General Public License v3.0
Author : projectcaluma
    def get_ordering_value(
        self, qs: QuerySet, value: Any
    ) -> Tuple[QuerySet, OrderingFieldType]:
        # First, join the requested answer, then annotate the QS accordingly.
        # Last, return a field corresponding to the value
        #
        question = Question.objects.get(pk=value)
        QUESTION_TYPE_TO_FIELD = {
            Question.TYPE_INTEGER: "value",
            Question.TYPE_FLOAT: "value",
            Question.TYPE_DATE: "date",
            Question.TYPE_CHOICE: "value",
            Question.TYPE_TEXTAREA: "value",
            Question.TYPE_TEXT: "value",
            Question.TYPE_FILE: "file",
            Question.TYPE_DYNAMIC_CHOICE: "value",
            Question.TYPE_STATIC: "value",
        }

        try:
            value_field = QUESTION_TYPE_TO_FIELD[question.type]
        except KeyError:  # pragma: no cover
            raise exceptions.ValidationError(
                f"Question '{question.slug}' has unsupported type {question.type} for ordering"
            )

        answers_subquery = Subquery(
            Answer.objects.filter(
                question=question,
                docameent=OuterRef(f"{self._docameent_locator_prefix}pk"),
            ).values(value_field)
        )
        ann_name = f"order_{value}"

        qs = qs.annotate(**{ann_name: answers_subquery})

        # TODO: respect docameent_via
        return qs, F(ann_name)

0 View Complete Implementation : filters.py
Copyright GNU General Public License v3.0
Author : projectcaluma
    @staticmethod
    def filter_order_by_question_answer_value(queryset, _, question_slug):
        order_by = "-order_value" if question_slug.startswith("-") else "order_value"
        question_slug = question_slug.lstrip("-")

        # Based on question type, set answer field to use for sorting
        not_supported = (Question.TYPE_TABLE,)
        question = Question.objects.get(slug=question_slug)
        answer_value = "value"
        if question.type in not_supported:
            raise RuntimeError(
                f'Questions with type "{question.type}" are not supported '
                f'by "filterOrderByQuestionAnswerValue"'
            )
        elif question.type == Question.TYPE_DATE:
            answer_value = "date"
        elif question.type == Question.TYPE_FILE:
            answer_value = "file__name"

        # Initialize subquery
        answers = Answer.objects.filter(
            question=question, docameent=OuterRef("docameent")
        )

        # Annotate the cases in the queryset with the value of the answer of the given
        # question and order by it.
        return queryset.annotate(
            order_value=Subquery(answers.values(answer_value)[:1])
        ).order_by(order_by)