Python django.db.models.Count() Examples

The following are 30 code examples of django.db.models.Count(). 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 also want to check out all available functions/classes of the module django.db.models , or try the search function .
Example #1
Source File: ScheduleViewset.py    From kobo-predict with BSD 2-Clause "Simplified" License 8 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 #2
Source File: commands.py    From django-badgify with 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 #3
Source File: viewsets.py    From contratospr-api with 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 #4
Source File: filters.py    From contratospr-api with 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 #5
Source File: filters.py    From contratospr-api with 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 #6
Source File: filters.py    From contratospr-api with 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 #7
Source File: tests_views.py    From koku with 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 #8
Source File: tests_views.py    From koku with 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 #9
Source File: tests_views.py    From koku with 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 #10
Source File: tests_views.py    From koku with 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 #11
Source File: tests_views.py    From koku with 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 #12
Source File: FieldSightXformViewset.py    From kobo-predict with 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
Source File: write.py    From django-cachalot with 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
Source File: models.py    From arguman.org with 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
Source File: shipments.py    From Servo with 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
Source File: 0019_auto_20160930_2351.py    From controller with 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
Source File: managers.py    From puput with 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
Source File: models.py    From figures with 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
Source File: models.py    From figures with 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
Source File: models.py    From figures with 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
Source File: views.py    From polls-api with 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
Source File: context_processors.py    From pyconkr-2015 with 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
Source File: models.py    From crowdata with 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
Source File: admin.py    From crowdata with MIT License 5 votes vote down vote up
def queryset(self, request):
        return models.Document.objects.annotate(entries_count=Count('form_entries')) 
Example #25
Source File: utils.py    From normandy with 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
Source File: public.py    From donation-tracker with 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
Source File: donation.py    From donation-tracker with 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
Source File: locus_list_api.py    From seqr with 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
Source File: dashboard_api.py    From seqr with 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
Source File: views.py    From coursys with 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)