Python django.db.models.Count() Examples

def filter_queryset(self, queryset):
        if self.request.user.is_anonymous():
            self.permission_denied(self.request)
        is_project = self.kwargs.get('is_project', None)
        pk = self.kwargs.get('pk', None)
        if is_project == "1":
            queryset = queryset.filter(project__id=pk)
            return queryset.annotate(response_count=Count("schedule_forms__project_form_instances")).select_related('schedule_forms', 'schedule_forms__xf', 'schedule_forms__em')
        else:
            project_id = get_object_or_404(Site, pk=pk).project.id
            queryset = queryset.filter(Q(site__id=pk, schedule_forms__from_project=False)
                                       | Q(project__id=project_id))
            return queryset.annotate(
                site_response_count=Count("schedule_forms__site_form_instances", ),
                response_count=Count(Case(
                    When(project__isnull=False, schedule_forms__project_form_instances__site__id=pk, then=F('schedule_forms__project_form_instances')),
                    output_field=IntegerField(),
                ), distinct=True)

            ).select_related('schedule_forms','schedule_forms__xf', 'schedule_forms__em') 

def show_stats(**kwargs):
    """
    Shows badges stats.
    """
    db_read = kwargs.get('db_read', DEFAULT_DB_ALIAS)

    badges = (Badge.objects.using(db_read)
                           .all()
                           .annotate(u_count=Count('users'))
                           .order_by('u_count'))

    for badge in badges:
        logger.info('{:<20} {:>10} users awarded | users_count: {})'.format(
            badge.name,
            badge.u_count,
            badge.users_count)) 

def spending_over_time(self, request):
        fiscal_year = request.query_params.get("fiscal_year")

        queryset = self.filter_queryset(self.get_queryset())

        if fiscal_year:
            start_date, end_date = get_fiscal_year_range(int(fiscal_year))
            queryset = queryset.filter(
                date_of_grant__gte=start_date, date_of_grant__lte=end_date
            )

        queryset = (
            queryset.without_amendments()
            .annotate(month=TruncMonth("date_of_grant"))
            .values("month")
            .annotate(total=Sum("amount_to_pay"), count=Count("id"))
            .values("month", "total", "count")
            .order_by("month")
        )

        return Response(queryset) 

def filter_entities(self, queryset, name, value):
        if not value:
            return queryset
        return (
            queryset.filter(contract__entity__in=value)
            .distinct()
            .annotate(
                contracts_total=Sum(
                    "contract__amount_to_pay",
                    filter=Q(contract__parent=None, contract__entity__in=value),
                ),
                contracts_count=Count(
                    "contract",
                    filter=Q(contract__parent=None, contract__entity__in=value),
                ),
            )
        ) 

def filter_entity_by_id(self, queryset, name, value):
        if not value:
            return queryset

        return (
            queryset.filter(contract__entity_id__in=value)
            .distinct()
            .annotate(
                contracts_total=Sum(
                    "contract__amount_to_pay",
                    filter=Q(contract__parent=None, contract__entity_id__in=value),
                ),
                contracts_count=Count(
                    "contract",
                    filter=Q(contract__parent=None, contract__entity_id__in=value),
                ),
            )
        ) 

def filter_contractors_by_id(self, queryset, name, value):
        if not value:
            return queryset

        contracts = Contract.objects.filter(contractors__in=value).only("id")

        return (
            queryset.filter(contract__in=contracts)
            .distinct()
            .annotate(
                contracts_total=Sum(
                    "contract__amount_to_pay",
                    filter=Q(contract__parent=None, contract__in=contracts),
                ),
                contracts_count=Count(
                    "contract", filter=Q(contract__parent=None, contract__in=contracts)
                ),
            )
        ) 

def test_execute_query_ocp_aws_costs_group_by_project(self):
        """Test that grouping by project filters data."""
        with tenant_context(self.tenant):
            # Force Django to do GROUP BY to get nodes
            projects = (
                OCPAWSCostLineItemDailySummary.objects.filter(usage_start__gte=self.ten_days_ago)
                .values(*["namespace"])
                .annotate(project_count=Count("namespace"))
                .all()
            )
            project_of_interest = projects[0].get("namespace")

        url = reverse("reports-openshift-aws-costs")
        client = APIClient()
        params = {"group_by[project]": project_of_interest}

        url = url + "?" + urlencode(params, quote_via=quote_plus)
        response = client.get(url, **self.headers)
        self.assertEqual(response.status_code, status.HTTP_200_OK)

        data = response.json()
        for entry in data.get("data", []):
            for project in entry.get("projects", []):
                self.assertEqual(project.get("project"), project_of_interest) 

def test_execute_query_ocp_aws_instance_type_by_project(self):
        """Test that the instance type API runs when grouped by project."""
        with tenant_context(self.tenant):
            # Force Django to do GROUP BY to get nodes
            projects = (
                OCPAWSCostLineItemDailySummary.objects.filter(usage_start__gte=self.ten_days_ago)
                .values(*["namespace"])
                .annotate(project_count=Count("namespace"))
                .all()
            )
            self.assertNotEqual(len(projects), 0)
            project_of_interest = projects[0].get("namespace")

        url = reverse("reports-openshift-aws-instance-type")
        client = APIClient()
        params = {"group_by[project]": project_of_interest}

        url = url + "?" + urlencode(params, quote_via=quote_plus)
        response = client.get(url, **self.headers)
        self.assertEqual(response.status_code, status.HTTP_200_OK)

        data = response.json()
        for entry in data.get("data", []):
            for project in entry.get("projects", []):
                self.assertEqual(project.get("project"), project_of_interest) 

def test_execute_query_group_by_project(self):
        """Test that grouping by project filters data."""
        with tenant_context(self.tenant):
            # Force Django to do GROUP BY to get nodes
            projects = (
                OCPUsageLineItemDailySummary.objects.filter(usage_start__gte=self.ten_days_ago.date())
                .values(*["namespace"])
                .annotate(project_count=Count("namespace"))
                .all()
            )
            project_of_interest = projects[0].get("namespace")

        url = reverse("reports-openshift-cpu")
        client = APIClient()
        params = {"group_by[project]": project_of_interest}

        url = url + "?" + urlencode(params, quote_via=quote_plus)
        response = client.get(url, **self.headers)
        self.assertEqual(response.status_code, status.HTTP_200_OK)

        data = response.json()
        for entry in data.get("data", []):
            for project in entry.get("projects", []):
                self.assertEqual(project.get("project"), project_of_interest) 

def test_execute_query_group_by_cluster(self):
        """Test that grouping by cluster filters data."""
        with tenant_context(self.tenant):
            # Force Django to do GROUP BY to get nodes
            clusters = (
                OCPUsageLineItemDailySummary.objects.filter(usage_start__gte=self.ten_days_ago.date())
                .values(*["cluster_id"])
                .annotate(cluster_count=Count("cluster_id"))
                .all()
            )
            cluster_of_interest = clusters[0].get("cluster_id")

        url = reverse("reports-openshift-cpu")
        client = APIClient()
        params = {"group_by[cluster]": cluster_of_interest}

        url = url + "?" + urlencode(params, quote_via=quote_plus)
        response = client.get(url, **self.headers)
        self.assertEqual(response.status_code, status.HTTP_200_OK)

        data = response.json()
        for entry in data.get("data", []):
            for cluster in entry.get("clusters", []):
                self.assertEqual(cluster.get("cluster"), cluster_of_interest) 

def test_execute_query_group_by_node(self):
        """Test that grouping by node filters data."""
        with tenant_context(self.tenant):
            # Force Django to do GROUP BY to get nodes
            nodes = (
                OCPUsageLineItemDailySummary.objects.values(*["node"])
                .filter(usage_start__gte=self.ten_days_ago.date())
                .values(*["node"])
                .annotate(node_count=Count("node"))
                .all()
            )
            node_of_interest = nodes[0].get("node")

        url = reverse("reports-openshift-cpu")
        client = APIClient()
        params = {"group_by[node]": node_of_interest}

        url = url + "?" + urlencode(params, quote_via=quote_plus)
        response = client.get(url, **self.headers)
        self.assertEqual(response.status_code, status.HTTP_200_OK)

        data = response.json()
        for entry in data.get("data", []):
            for node in entry.get("nodes", []):
                self.assertIn(node.get("node"), node_of_interest) 

def filter_queryset(self, queryset):
        if self.request.user.is_anonymous():
            self.permission_denied(self.request)
        is_project = self.kwargs.get('is_project', None)
        pk = self.kwargs.get('pk', None)
        if is_project == "1":
            queryset = queryset.filter(project__id=pk)
            return queryset.select_related('xf', 'em')
        else:
            project_id = get_object_or_404(Site, pk=pk).project.id
            queryset = queryset.filter(Q(site__id=pk, from_project=False)
                                       | Q (project__id=project_id))
            return queryset.annotate(
                site_response_count=Count("site_form_instances",),
                response_count=Count(Case(
                    When(project__isnull=False, project_form_instances__site__id=pk, then=F('project_form_instances')),
                    output_field=IntegerField(),
                ), distinct=True)

            ).select_related('xf', 'em') 

def test_invalidate_aggregate(self):
        with self.assertNumQueries(1):
            self.assertEqual(User.objects.aggregate(n=Count('test'))['n'], 0)

        with self.assertNumQueries(2 if self.is_dj_21_below_and_is_sqlite() else 1):
            u = User.objects.create_user('test')
        with self.assertNumQueries(1):
            self.assertEqual(User.objects.aggregate(n=Count('test'))['n'], 0)

        with self.assertNumQueries(2 if self.is_dj_21_below_and_is_sqlite() else 1):
            Test.objects.create(name='test1')
        with self.assertNumQueries(1):
            self.assertEqual(User.objects.aggregate(n=Count('test'))['n'], 0)

        with self.assertNumQueries(2 if self.is_dj_21_below_and_is_sqlite() else 1):
            Test.objects.create(name='test2', owner=u)
        with self.assertNumQueries(1):
            self.assertEqual(User.objects.aggregate(n=Count('test'))['n'], 1)

        with self.assertNumQueries(2 if self.is_dj_21_below_and_is_sqlite() else 1):
            Test.objects.create(name='test3')
        with self.assertNumQueries(1):
            self.assertEqual(User.objects.aggregate(n=Count('test'))['n'], 1) 

def calculate_karma(self):
        # CALCULATES THE KARMA POINT OF USER
        # ACCORDING TO HOW MANY TIMES SUPPORTED * 2
        # DECREASE BY FALLACY COUNT * 2
        # HOW MANY CHILD PREMISES ARE ADDED TO USER'S PREMISES
        karma = 0
        support_sum = self.user_premises.aggregate(Count('supporters'))
        karma += 2 * support_sum['supporters__count']
        main_premises = self.user_premises.all()
        all_sub_premises = []
        for premise in main_premises:
            all_sub_premises += premise.published_children().values_list('pk',
                                                                         flat=True)
            karma -= 2 * (premise.reports.count())
        not_owned_sub_premises = Premise.objects.\
            filter(id__in=all_sub_premises).\
            exclude(user__id=self.id).count()
        karma += not_owned_sub_premises
        return karma 

def list_bulk_returns(request):
    from django.db.models import Count
    title = _("Browse Bulk Returns")
    returns = Shipment.objects.exclude(dispatched_at=None).annotate(num_parts=Count('servicepart'))

    page = request.GET.get("page")
    returns = paginate(returns, page, 50)
    counts = prep_counts()

    return render(request, "shipments/list_bulk_returns.html", locals()) 

def fix_duplicate_keys(apps, schema_editor):
    Keys = apps.get_model("api", "Key")

    # find duplicates
    duplicates = Keys.objects.values('id') \
                            .annotate(Count('id')) \
                            .order_by() \
                            .filter(id__count__gt=1)
    for dup in duplicates:
        # update all duplicates
        inc = 1
        for key in Keys.objects.filter(id=dup['id']):
            key_id = '{}-{}'.format(key.id, inc)
            key.id = key_id
            key.save()
            inc += 1 

def most_common(self, blog_page):
        entries = blog_page.get_entries()
        return self.filter(entrypage__in=entries).annotate(num_times=Count('entrypage')).order_by('-num_times') 

def enrollment_counts(self, course_id):

        query = super(CourseEnrollmentManager, self).get_queryset().filter(
                      course_id=course_id, is_active=True).values(
                      'mode').order_by().annotate(models.Count('mode'))
        total = 0
        enroll_dict = defaultdict(int)
        for item in query:
            enroll_dict[item['mode']] = item['mode__count']
            total += item['mode__count']
        enroll_dict['total'] = total
        return enroll_dict 

def num_enrolled_in_exclude_admins(self, course_id):
        """
        Returns the count of active enrollments in a course excluding instructors, staff and CCX coaches.

        Arguments:
            course_id (CourseLocator): course_id to return enrollments (count).

        Returns:
            int: Count of enrollments excluding staff, instructors and CCX coaches.

        """
        # To avoid circular imports.
        from student.roles import CourseCcxCoachRole, CourseInstructorRole, CourseStaffRole
        course_locator = course_id

        if getattr(course_id, 'ccx', None):
            # We don't use CCX, so raising exception rather than support it
            raise Exception('CCX is not supported')

        staff = CourseStaffRole(course_locator).users_with_role()
        admins = CourseInstructorRole(course_locator).users_with_role()
        coaches = CourseCcxCoachRole(course_locator).users_with_role()

        qs = super(CourseEnrollmentManager, self).get_queryset()
        q2 = qs.filter(course_id=course_id, is_active=1)
        q3 = q2.exclude(user__in=staff).exclude(user__in=admins).exclude(user__in=coaches)
        return q3.count() 

def enrollment_counts(self, course_id):

        query = super(CourseEnrollmentManager, self).get_queryset().filter(
                      course_id=course_id, is_active=True).values(
                      'mode').order_by().annotate(models.Count('mode'))
        total = 0
        enroll_dict = defaultdict(int)
        for item in query:
            enroll_dict[item['mode']] = item['mode__count']
            total += item['mode__count']
        enroll_dict['total'] = total
        return enroll_dict 

def get_relations(self):
        choices = self.get_object().choices.annotate(vote_count=Count('votes')).order_by('-vote_count', 'choice_text')

        def choice_resource(choice):
            resource = ChoiceResource()
            resource.obj = choice
            resource.request = getattr(self, 'request', None)
            return resource

        return {
            'choices': list(map(choice_resource, choices)),
        } 

def sponsors(request):
    levels = SponsorLevel.objects.annotate(
        num_sponsors=Count('sponsor')).filter(num_sponsors__gt=0)

    return {
        'levels': levels,
    } 

def leaderboard(self):
        """ Returns a queryset of the biggest contributors (User) to this DocumentSet """
        return User.objects.filter(documentsetformentry__form__document_set=self).annotate(num_entries=Count('documentsetformentry')).order_by('-num_entries') 

def queryset(self, request):
        return models.Document.objects.annotate(entries_count=Count('form_entries')) 

def filter_m2m(qs, field, values):
    """
    Filters a queryset by an exact list of many to many relations.
    """
    values = list(values)

    qs = qs.annotate(_count=Count(field)).filter(_count=len(values))

    if len(values):
        qs = qs.filter(**{"{}__in".format(field): values})

    return qs 

def index(request, event=None):
    event = viewutil.get_event(event)
    eventParams = {}

    if event.id:
        eventParams['event'] = event.id

    agg = Donation.objects.filter(
        transactionstate='COMPLETED', testdonation=False, **eventParams
    ).aggregate(
        amount=Cast(Coalesce(Sum('amount'), 0), output_field=FloatField()),
        count=Count('amount'),
        max=Cast(Coalesce(Max('amount'), 0), output_field=FloatField()),
        avg=Cast(Coalesce(Avg('amount'), 0), output_field=FloatField()),
    )
    agg['target'] = float(event.targetamount)
    count = {
        'runs': filters.run_model_query('run', eventParams).count(),
        'prizes': filters.run_model_query('prize', eventParams).count(),
        'bids': filters.run_model_query('bid', eventParams).count(),
        'donors': filters.run_model_query('donorcache', eventParams)
        .values('donor')
        .distinct()
        .count(),
    }

    if 'json' in request.GET:
        return HttpResponse(
            json.dumps({'count': count, 'agg': agg}, ensure_ascii=False,),
            content_type='application/json;charset=utf-8',
        )

    return views_common.tracker_response(
        request, 'tracker/index.html', {'agg': agg, 'count': count, 'event': event}
    ) 

def update(self):
        aggregate = Donation.objects.filter(
            donor=self.donor, transactionstate='COMPLETED'
        )
        if self.event:
            aggregate = aggregate.filter(event=self.event)
        aggregate = aggregate.aggregate(
            total=Coalesce(Sum('amount'), 0.0),
            count=Coalesce(Count('amount'), 0),
            max=Coalesce(Max('amount'), 0.0),
            avg=Coalesce(Avg('amount'), 0.0),
        )
        self.donation_total = aggregate['total']
        self.donation_count = aggregate['count']
        self.donation_max = aggregate['max']
        self.donation_avg = aggregate['avg'] 

def locus_lists(request):
    if request.user.is_staff:
        locus_list_models = LocusList.objects.all()
    else:
        locus_list_models = LocusList.objects.filter(Q(is_public=True) | Q(created_by=request.user))
    locus_list_models = locus_list_models.annotate(num_projects=Count('projects'))

    locus_lists_json = get_json_for_locus_lists(locus_list_models, request.user, include_project_count=True)

    return create_json_response({
        'locusListsByGuid': {locus_list['locusListGuid']: locus_list for locus_list in locus_lists_json}
    }) 

def _get_projects_json(user):
    projects = get_projects_user_can_view(user)
    if not projects:
        return {}

    projects_with_counts = projects.annotate(
        models.Count('family', distinct=True), models.Count('family__individual', distinct=True),
        models.Count('family__savedvariant', distinct=True))

    projects_by_guid = {p['projectGuid']: p for p in get_json_for_projects(projects, user=user)}
    for project in projects_with_counts:
        projects_by_guid[project.guid]['numFamilies'] = project.family__count
        projects_by_guid[project.guid]['numIndividuals'] = project.family__individual__count
        projects_by_guid[project.guid]['numVariantTags'] = project.family__savedvariant__count

    analysis_status_counts = Family.objects.filter(project__in=projects).values(
        'project__guid', 'analysis_status').annotate(count=models.Count('*'))
    for agg in analysis_status_counts:
        project_guid = agg['project__guid']
        if 'analysisStatusCounts' not in projects_by_guid[project_guid]:
            projects_by_guid[project_guid]['analysisStatusCounts'] = {}
        projects_by_guid[project_guid]['analysisStatusCounts'][agg['analysis_status']] = agg['count']

    sample_type_status_counts = Sample.objects.filter(individual__family__project__in=projects, dataset_type=Sample.DATASET_TYPE_VARIANT_CALLS
    ).values(
        'individual__family__project__guid', 'sample_type',
    ).annotate(count=models.Count('individual_id', distinct=True))
    for agg in sample_type_status_counts:
        project_guid = agg['individual__family__project__guid']
        if 'sampleTypeCounts' not in projects_by_guid[project_guid]:
            projects_by_guid[project_guid]['sampleTypeCounts'] = {}
        projects_by_guid[project_guid]['sampleTypeCounts'][agg['sample_type']] = agg['count']

    return projects_by_guid 

def index_full(request):
    userid = request.user.username
    memberships = Member.objects.exclude(role="DROP").exclude(offering__component="CAN") \
            .filter(offering__graded=True, person__userid=userid) \
            .annotate(num_activities=Count('offering__activity')) \
            .select_related('offering','offering__semester')
    memberships = [m for m in memberships if m.role in ['TA', 'INST', 'APPR'] or m.num_activities>0]

    context = {'memberships': memberships}
    return render(request, "dashboard/index_full.html", context)