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 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_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_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 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_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_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_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_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 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 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_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 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 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 test_annotate(self):
        Test.objects.create(name='test3', owner=self.user)
        qs = (User.objects.annotate(n=Count('test')).order_by('pk')
              .values_list('n', flat=True))
        self.assert_tables(qs, User, Test)
        self.assert_query_cached(qs, [2, 1]) 

def test_execute_query_ocp_aws_storage_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 = (
                OCPAWSCostLineItemDailySummary.objects.filter(usage_start__gte=self.ten_days_ago)
                .filter(product_family__contains="Storage")
                .values(*["cluster_id"])
                .annotate(cluster_count=Count("cluster_id"))
                .all()
            )
            self.assertNotEqual(len(clusters), 0)
            cluster_of_interest = clusters[0].get("cluster_id")

        url = reverse("reports-openshift-aws-storage")
        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 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 supported_premise_count(self):
        return self.premise_set.aggregate(Count('supporters'))[
            'supporters__count'] 

def get_active_users(self):
        channel = self.get_channel()
        nouns = channel.nouns.all()
        return Profile.objects.filter(
            user_premises__argument__nouns__in=nouns,
            user_premises__argument__is_published=True,
            user_premises__date_creation__gte=datetime.today() - timedelta(days=30)
        ).annotate(
            score=Count('user_premises__argument', distinct=True)
        ).order_by(
            '-score'
        )[:5] 

def setup(self):
        defaults = dict(name='Matkailu- ja kongressitoimisto')
        self.data_source, _ = DataSource.objects.get_or_create(id=self.name, defaults=defaults)
        self.tprek_data_source = DataSource.objects.get(id='tprek')

        ytj_ds, _ = DataSource.objects.get_or_create(defaults={'name': 'YTJ'}, id='ytj')

        org_args = dict(origin_id='0586977-6', data_source=ytj_ds)
        defaults = dict(name='Helsingin Markkinointi Oy')

        self.organization, _ = Organization.objects.get_or_create(
            defaults=defaults, **org_args)

        place_list = Place.objects.filter(data_source=self.tprek_data_source, deleted=False)
        deleted_place_list = Place.objects.filter(data_source=self.tprek_data_source,
                                                  deleted=True)
        # Get only places that have unique names
        place_list = place_list.annotate(count=Count('name_fi')).filter(count=1).values('id', 'origin_id', 'name_fi')
        deleted_place_list = deleted_place_list.annotate(count=Count('name_fi')).\
            filter(count=1).values('id', 'origin_id', 'name_fi', 'replaced_by_id')
        self.tprek_by_name = {p['name_fi'].lower(): (p['id'], p['origin_id']) for p in place_list}
        self.deleted_tprek_by_name = {
            p['name_fi'].lower(): (p['id'], p['origin_id'], p['replaced_by_id'])
            for p in deleted_place_list}

        if self.options['cached']:
            requests_cache.install_cache('matko') 

def test_invalidate_annotate(self):
        with self.assertNumQueries(1):
            data1 = list(User.objects.annotate(n=Count('test')).order_by('pk'))
        self.assertListEqual(data1, [])

        with self.assertNumQueries(2 if self.is_dj_21_below_and_is_sqlite() else 1):
            Test.objects.create(name='test1')
        with self.assertNumQueries(1):
            data2 = list(User.objects.annotate(n=Count('test')).order_by('pk'))
        self.assertListEqual(data2, [])

        with self.assertNumQueries(4 if self.is_dj_21_below_and_is_sqlite() else 2):
            user1 = User.objects.create_user('user1')
            user2 = User.objects.create_user('user2')
        with self.assertNumQueries(1):
            data3 = list(User.objects.annotate(n=Count('test')).order_by('pk'))
        self.assertListEqual(data3, [user1, user2])
        self.assertListEqual([u.n for u in data3], [0, 0])

        with self.assertNumQueries(2 if self.is_dj_21_below_and_is_sqlite() else 1):
            Test.objects.create(name='test2', owner=user1)
        with self.assertNumQueries(1):
            data4 = list(User.objects.annotate(n=Count('test')).order_by('pk'))
        self.assertListEqual(data4, [user1, user2])
        self.assertListEqual([u.n for u in data4], [1, 0])

        with self.assertNumQueries(2 if self.is_sqlite else 1):
            Test.objects.bulk_create([
                Test(name='test3', owner=user1),
                Test(name='test4', owner=user2),
                Test(name='test5', owner=user1),
                Test(name='test6', owner=user2),
            ])
        with self.assertNumQueries(1):
            data5 = list(User.objects.annotate(n=Count('test')).order_by('pk'))
        self.assertListEqual(data5, [user1, user2])
        self.assertListEqual([u.n for u in data5], [3, 2]) 

def test_having(self):
        qs = (User.objects.annotate(n=Count('user_permissions'))
              .filter(n__gte=1))
        self.assert_tables(qs, User, User.user_permissions.through, Permission)
        self.assert_query_cached(qs, [self.user])

        with self.assertNumQueries(1):
            self.assertEqual(User.objects.annotate(n=Count('user_permissions'))
                             .filter(n__gte=1).count(), 1)

        with self.assertNumQueries(0):
            self.assertEqual(User.objects.annotate(n=Count('user_permissions'))
                             .filter(n__gte=1).count(), 1) 

def get_supported_premises(self, user):
        return Premise.objects.filter(
            is_approved=True,
            user=user,
            argument__language=normalize_language_code(get_language())
        ).annotate(
            supporter_count=Count('supporters', distinct=True)
        ).filter(
            supporter_count__gt=0
        ).order_by(
            '-supporter_count'
        )[:10] 

def get_submissions_count(self):
        qs = self.project_instances.aggregate(
            outstanding=Count(Case(When(form_status=0, project=self, then=1), output_field=IntegerField(),)),
            flagged=Count(Case(When(form_status=2, project=self, then=1), output_field=IntegerField(),)),
            approved=Count(Case(When(form_status=3, project=self, then=1), output_field=IntegerField(),)),
            rejected=Count(Case(When(form_status=1, project=self, then=1), output_field=IntegerField(),)),
        )
        return qs.get('outstanding', 0), qs.get('flagged', 0), qs.get('approved', 0), qs.get('rejected', 0) 

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("project_form_instances")).select_related('xf', 'em')
        return [] 

def test_annotation(self):
        from django.db.models import Count
        artifact = Artifact.objects.create(name="generic artifact 6")
        artifact.relations.add(self.article1)
        artifact.relations.add(self.article2)
        artifacts = Artifact.objects.annotate(Count('articles')).all()
        self.assertEqual(artifacts[0].articles__count, 2) 

def get_relay_history_stats(self, from_date: datetime.datetime = None,
                                to_date: datetime.datetime = None) -> Dict[str, Any]:

        from_date = from_date if from_date else datetime.datetime(2018, 11, 1, tzinfo=utc)
        to_date = to_date if to_date else timezone.now()

        def add_time_filter(queryset):
            return queryset.filter(created__range=(from_date, to_date))

        return {
            'safes_created': {
                'deployed': add_time_filter(SafeContract.objects.deployed()).annotate(
                    created_date=TruncDate('created')).values('created_date').annotate(number=Count('*')
                                                                                       ).order_by('created_date'),
                # 'average_deploy_time_seconds': SafeContract.objects.get_average_deploy_time_grouped(from_date, to_date),
                # 'average_deploy_time_total_seconds':
                #    SafeContract.objects.get_average_deploy_time_total_grouped(from_date, to_date),
                'payment_tokens': SafeContract.objects.get_creation_tokens_usage_grouped(from_date, to_date),
            },
            'relayed_txs': {
                'total': add_time_filter(SafeMultisigTx.objects.annotate(
                    created_date=TruncDate('created')).values('created_date').annotate(number=Count('*')
                                                                                       ).order_by('created_date')),
                'average_execution_time_seconds': SafeMultisigTx.objects.get_average_execution_time_grouped(from_date,
                                                                                                            to_date),
                'payment_tokens': add_time_filter(SafeMultisigTx.objects.get_tokens_usage_grouped()),
            }
        } 

def setUp(self):
        """Set up the customer view tests."""
        self.dh = DateHelper()
        super().setUp()
        with tenant_context(self.tenant):
            self.accounts = AWSCostEntryLineItemDailySummary.objects.values("usage_account_id").distinct()
            self.accounts = [entry.get("usage_account_id") for entry in self.accounts]

            self.services = AWSCostEntryLineItemDailySummary.objects.values("product_code").distinct()
            self.services = [entry.get("product_code") for entry in self.services]

            self.availability_zone = (
                AWSCostEntryLineItemDailySummary.objects.filter(availability_zone__isnull=False)
                .values("availability_zone")
                .distinct()
                .first()
                .get("availability_zone")
            )
            self.availability_zone_count = AWSCostEntryLineItemDailySummary.objects.aggregate(
                Count("availability_zone", distinct=True)
            ).get("availability_zone__count")
            self.region = AWSCostEntryLineItemDailySummary.objects.values("region").distinct().first().get("region")
            self.region_count = AWSCostEntryLineItemDailySummary.objects.aggregate(Count("region", distinct=True)).get(
                "region__count"
            )
            self.account_alias = (
                AWSCostEntryLineItemDailySummary.objects.values("account_alias__account_alias")
                .distinct()
                .first()
                .get("account_alias__account_alias")
            )
            self.account_aliases = get_account_ailases()[0]
            self.account_alias_mapping = get_account_ailases()[1]
            self.organizational_unit = (
                AWSCostEntryLineItemDailySummary.objects.values("organizational_unit__org_unit_id")
                .distinct()
                .first()
                .get("organizational_unit__org_unit_id")
            ) 

def test_execute_query_ocp_aws_storage_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)
                .filter(product_family__contains="Storage")
                .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-storage")
        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_storage_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 = (
                OCPAWSCostLineItemDailySummary.objects.values(*["node"])
                .filter(usage_start__gte=self.ten_days_ago)
                .filter(product_family__contains="Storage")
                .values(*["node"])
                .annotate(node_count=Count("node"))
                .all()
            )
            self.assertNotEqual(len(nodes), 0)
            node_of_interest = nodes[0].get("node")

        url = reverse("reports-openshift-aws-storage")
        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.assertEqual(node.get("node"), node_of_interest)