Python django.db.models.Count() Examples

The following are 30 code examples for showing how to use django.db.models.Count(). These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example.

You may check out the related API usage on the sidebar.

You may also want to check out all available functions/classes of the module django.db.models , or try the search function .

Example 1
Project: django-badgify   Author: ulule   File: commands.py    License: MIT License 6 votes vote down vote up
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)) 
Example 2
Project: contratospr-api   Author: Code4PuertoRico   File: viewsets.py    License: Apache License 2.0 6 votes vote down vote up
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) 
Example 3
Project: contratospr-api   Author: Code4PuertoRico   File: filters.py    License: Apache License 2.0 6 votes vote down vote up
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),
                ),
            )
        ) 
Example 4
Project: contratospr-api   Author: Code4PuertoRico   File: filters.py    License: Apache License 2.0 6 votes vote down vote up
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),
                ),
            )
        ) 
Example 5
Project: contratospr-api   Author: Code4PuertoRico   File: filters.py    License: Apache License 2.0 6 votes vote down vote up
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)
                ),
            )
        ) 
Example 6
Project: koku   Author: project-koku   File: tests_views.py    License: GNU Affero General Public License v3.0 6 votes vote down vote up
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) 
Example 7
Project: koku   Author: project-koku   File: tests_views.py    License: GNU Affero General Public License v3.0 6 votes vote down vote up
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) 
Example 8
Project: koku   Author: project-koku   File: tests_views.py    License: GNU Affero General Public License v3.0 6 votes vote down vote up
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) 
Example 9
Project: koku   Author: project-koku   File: tests_views.py    License: GNU Affero General Public License v3.0 6 votes vote down vote up
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) 
Example 10
Project: koku   Author: project-koku   File: tests_views.py    License: GNU Affero General Public License v3.0 6 votes vote down vote up
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) 
Example 11
Project: kobo-predict   Author: awemulya   File: ScheduleViewset.py    License: BSD 2-Clause "Simplified" License 6 votes vote down vote up
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') 
Example 12
Project: kobo-predict   Author: awemulya   File: FieldSightXformViewset.py    License: BSD 2-Clause "Simplified" License 6 votes vote down vote up
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') 
Example 13
Project: django-cachalot   Author: noripyt   File: write.py    License: BSD 3-Clause "New" or "Revised" License 6 votes vote down vote up
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) 
Example 14
Project: arguman.org   Author: arguman   File: models.py    License: GNU Affero General Public License v3.0 6 votes vote down vote up
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 
Example 15
Project: Servo   Author: fpsw   File: shipments.py    License: BSD 2-Clause "Simplified" License 5 votes vote down vote up
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()) 
Example 16
Project: controller   Author: deis   File: 0019_auto_20160930_2351.py    License: MIT License 5 votes vote down vote up
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 
Example 17
Project: puput   Author: APSL   File: managers.py    License: MIT License 5 votes vote down vote up
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') 
Example 18
Project: figures   Author: appsembler   File: models.py    License: MIT License 5 votes vote down vote up
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 
Example 19
Project: figures   Author: appsembler   File: models.py    License: MIT License 5 votes vote down vote up
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() 
Example 20
Project: figures   Author: appsembler   File: models.py    License: MIT License 5 votes vote down vote up
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 
Example 21
Project: polls-api   Author: apiaryio   File: views.py    License: MIT License 5 votes vote down vote up
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)),
        } 
Example 22
Project: pyconkr-2015   Author: pythonkr   File: context_processors.py    License: MIT License 5 votes vote down vote up
def sponsors(request):
    levels = SponsorLevel.objects.annotate(
        num_sponsors=Count('sponsor')).filter(num_sponsors__gt=0)

    return {
        'levels': levels,
    } 
Example 23
Project: crowdata   Author: crowdata   File: models.py    License: MIT License 5 votes vote down vote up
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') 
Example 24
Project: crowdata   Author: crowdata   File: admin.py    License: MIT License 5 votes vote down vote up
def queryset(self, request):
        return models.Document.objects.annotate(entries_count=Count('form_entries')) 
Example 25
Project: normandy   Author: mozilla   File: utils.py    License: Mozilla Public License 2.0 5 votes vote down vote up
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 
Example 26
Project: donation-tracker   Author: GamesDoneQuick   File: public.py    License: Apache License 2.0 5 votes vote down vote up
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}
    ) 
Example 27
Project: donation-tracker   Author: GamesDoneQuick   File: donation.py    License: Apache License 2.0 5 votes vote down vote up
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'] 
Example 28
Project: seqr   Author: macarthur-lab   File: locus_list_api.py    License: GNU Affero General Public License v3.0 5 votes vote down vote up
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}
    }) 
Example 29
Project: seqr   Author: macarthur-lab   File: dashboard_api.py    License: GNU Affero General Public License v3.0 5 votes vote down vote up
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 
Example 30
Project: coursys   Author: sfu-fas   File: views.py    License: GNU General Public License v3.0 5 votes vote down vote up
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)