django.db.models.Count - python examples

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

145 Examples 7

1 2 3
3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_count(self):
        vals = Book.objects.aggregate(Count("rating"))
        self.astertEqual(vals, {"rating__count": 6})

        vals = Book.objects.aggregate(Count("rating", distinct=True))
        self.astertEqual(vals, {"rating__count": 4})

3 View Complete Implementation : models.py
Copyright MIT License
Author : vitorfs
    def update_click_rate(self) -> float:
        count = self.activities.values('email_id', 'activity_type').aggregate(
            sent=Count('email_id', distinct=True, filter=Q(activity_type=ActivityTypes.SENT)),
            clicked=Count('email_id', distinct=True, filter=Q(activity_type=ActivityTypes.CLICKED)),
        )
        try:
            self.click_rate = round(count['clicked'] / count['sent'], 4)
        except ZeroDivisionError:
            self.click_rate = 0.0
        finally:
            self.save(update_fields=['click_rate'])
        return self.click_rate

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_non_grouped_annotation_not_in_group_by(self):
        """
        An annotation not included in values() before an aggregate should be
        excluded from the group by clause.
        """
        qs = (
            Book.objects.annotate(xprice=F('price')).filter(rating=4.0).values('rating')
                .annotate(count=Count('publisher_id', distinct=True)).values('count', 'rating').order_by('count')
        )
        self.astertEqual(list(qs), [{'rating': 4.0, 'count': 2}])

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_annotate_with_aggregation(self):
        books = Book.objects.annotate(
            is_book=Value(1, output_field=IntegerField()),
            rating_count=Count('rating'))
        for book in books:
            self.astertEqual(book.is_book, 1)
            self.astertEqual(book.rating_count, 1)

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_deprecated_values_annotate(self):
        msg = (
            "Article QuerySet won't use Meta.ordering in Django 3.1. Add "
            ".order_by('-pub_date', 'headline') to retain the current query."
        )
        with self.astertRaisesMessage(RemovedInDjango31Warning, msg):
            list(Article.objects.values('author').annotate(Count('headline')))

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_ticket17424(self):
        """
        Doing exclude() on a foreign model after annotate() doesn't crash.
        """
        all_books = list(Book.objects.values_list('pk', flat=True).order_by('pk'))
        annotated_books = Book.objects.order_by('pk').annotate(one=Count("id"))

        # The value doesn't matter, we just need any negative
        # constraint on a related model that's a noop.
        excluded_books = annotated_books.exclude(publisher__name="__UNLIKELY_VALUE__")

        # Try to generate query tree
        str(excluded_books.query)

        self.astertQuerysetEqual(excluded_books, all_books, lambda x: x.pk)

        # Check internal state
        self.astertIsNone(annotated_books.query.alias_map["aggregation_book"].join_type)
        self.astertIsNone(excluded_books.query.alias_map["aggregation_book"].join_type)

3 View Complete Implementation : views.py
Copyright MIT License
Author : vitorfs
    def get_queryset(self):
        queryset = super().get_queryset()

        if self.request.GET.get('q', ''):
            query = self.request.GET.get('q').strip()
            queryset = queryset.filter(Q(name__icontains=query) | Q(description__icontains=query))
            self.extra_context = {
                'is_filtered': True,
                'query': query
            }

        queryset = queryset.annotate(subscribers_count=Count('subscribers'))
        return queryset.order_by('name')

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

3 View Complete Implementation : views.py
Copyright Apache License 2.0
Author : protwis
    def get_context_data (self, **kwargs):

        context = super(LigandBrowser, self).get_context_data(**kwargs)

        ligands = astayExperiment.objects.values(
            'protein__entry_name', 
            'protein__species__common_name',
            'protein__family__name',
            'protein__family__parent__name',
            'protein__family__parent__parent__name',
            'protein__family__parent__parent__parent__name',
            'protein__species__common_name'
            ).annotate(num_ligands=Count('ligand', distinct=True))

        context['ligands'] = ligands

        return context

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_values_aggregation(self):
        # Refs #20782
        max_rating = Book.objects.values('rating').aggregate(max_rating=Max('rating'))
        self.astertEqual(max_rating['max_rating'], 5)
        max_books_per_rating = Book.objects.values('rating').annotate(
            books_per_rating=Count('id')
        ).aggregate(Max('books_per_rating'))
        self.astertEqual(
            max_books_per_rating,
            {'books_per_rating__max': 3})

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_aggregation_expressions(self):
        a1 = Author.objects.aggregate(av_age=Sum('age') / Count('*'))
        a2 = Author.objects.aggregate(av_age=Sum('age') / Count('age'))
        a3 = Author.objects.aggregate(av_age=Avg('age'))
        self.astertEqual(a1, {'av_age': 37})
        self.astertEqual(a2, {'av_age': 37})
        self.astertEqual(a3, {'av_age': Approximate(37.4, places=1)})

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_ticket_16409(self):
        # Regression for #16409 - make sure defer() and only() work with annotate()
        self.astertIsInstance(
            list(SimpleItem.objects.annotate(Count('feature')).defer('name')),
            list)
        self.astertIsInstance(
            list(SimpleItem.objects.annotate(Count('feature')).only('name')),
            list)

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    @skipUnlessDBFeature("update_can_self_select")
    def test_ticket_19102_annotate(self):
        with self.astertNumQueries(1):
            Login.objects.order_by('description').filter(
                orgunit__name__isnull=False
            ).annotate(
                n=models.Count('description')
            ).filter(
                n=1, pk=self.l1.pk
            ).delete()
        self.astertFalse(Login.objects.filter(pk=self.l1.pk).exists())
        self.astertTrue(Login.objects.filter(pk=self.l2.pk).exists())

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_complex_aggregations_require_kwarg(self):
        with self.astertRaisesMessage(TypeError, 'Complex annotations require an alias'):
            Author.objects.annotate(Sum(F('age') + F('friends__age')))
        with self.astertRaisesMessage(TypeError, 'Complex aggregates require an alias'):
            Author.objects.aggregate(Sum('age') / Count('age'))
        with self.astertRaisesMessage(TypeError, 'Complex aggregates require an alias'):
            Author.objects.aggregate(Sum(1))

3 View Complete Implementation : test_expressions.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_multiple_annotation(self):
        multi_field = MultiFields.objects.create(
            point=Point(1, 1),
            city=City.objects.get(name='Houston'),
            poly=Polygon(((1, 1), (1, 2), (2, 2), (2, 1), (1, 1))),
        )
        qs = City.objects.values('name').annotate(
            distance=Min(functions.Distance('multifields__point', multi_field.city.point)),
        ).annotate(count=Count('multifields'))
        self.astertTrue(qs.first())

3 View Complete Implementation : models.py
Copyright MIT License
Author : vitorfs
    def update_open_rate(self) -> float:
        count = self.activities.values('email_id', 'activity_type').aggregate(
            sent=Count('email_id', distinct=True, filter=Q(activity_type=ActivityTypes.SENT)),
            opened=Count('email_id', distinct=True, filter=Q(activity_type=ActivityTypes.OPENED)),
        )
        try:
            self.open_rate = round(count['opened'] / count['sent'], 4)
        except ZeroDivisionError:
            self.open_rate = 0.0
        finally:
            self.save(update_fields=['open_rate'])
        return self.open_rate

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_defer_annotate_select_related(self):
        location = Location.objects.create()
        Request.objects.create(location=location)
        self.astertIsInstance(
            list(Request.objects.annotate(Count('items')).select_related('profile', 'location')
                 .only('profile', 'location')),
            list
        )
        self.astertIsInstance(
            list(Request.objects.annotate(Count('items')).select_related('profile', 'location')
                 .only('profile__profile1', 'location__location1')),
            list
        )
        self.astertIsInstance(
            list(Request.objects.annotate(Count('items')).select_related('profile', 'location')
                 .defer('request1', 'request2', 'request3', 'request4')),
            list
        )

3 View Complete Implementation : views.py
Copyright MIT License
Author : vitorfs
    def get_context_data(self, **kwargs):
        location_open_activities = Activity.objects \
            .filter(email__campaign_id=self.kwargs.get('pk'), activity_type=ActivityTypes.OPENED) \
            .values('location__country__code', 'location__country__name') \
            .annotate(total_opens=Count('id')) \
            .order_by('-total_opens')

        kwargs.update({
            'location_open_activities': location_open_activities,
        })
        return super().get_context_data(**kwargs)

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_update_annotated_multi_table_queryset(self):
        """
        Update of a queryset that's been annotated and involves multiple tables.
        """
        # Trivial annotated update
        qs = DataPoint.objects.annotate(related_count=Count('relatedpoint'))
        self.astertEqual(qs.update(value='Foo'), 3)
        # Update where annotation is used for filtering
        qs = DataPoint.objects.annotate(related_count=Count('relatedpoint'))
        self.astertEqual(qs.filter(related_count=1).update(value='Foo'), 1)
        # Update where annotation is used in update parameters
        # #26539 - This isn't forbidden but also doesn't generate proper SQL
        # qs = RelatedPoint.objects.annotate(data_name=F('data__name'))
        # updated = qs.update(name=F('data_name'))
        # self.astertEqual(updated, 1)
        # Update where aggregation annotation is used in update parameters
        qs = RelatedPoint.objects.annotate(max=Max('data__value'))
        with self.astertRaisesMessage(FieldError, 'Aggregate functions are not allowed in this query'):
            qs.update(name=F('max'))

3 View Complete Implementation : models.py
Copyright MIT License
Author : vitorfs
    def update_clicks_count(self) -> tuple:
        qs = self.activities.filter(activity_type=ActivityTypes.CLICKED) \
            .values('subscriber_id') \
            .order_by('subscriber_id') \
            .aggregate(unique_count=Count('subscriber_id', distinct=True), total_count=Count('subscriber_id'))
        self.unique_clicks_count = qs['unique_count']
        self.total_clicks_count = qs['total_count']
        self.save(update_fields=['unique_clicks_count', 'total_clicks_count'])
        return (self.unique_clicks_count, self.total_clicks_count)

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_ticket17424(self):
        """
        Doing exclude() on a foreign model after annotate() doesn't crash.
        """
        all_books = list(Book.objects.values_list('pk', flat=True).order_by('pk'))
        annotated_books = Book.objects.order_by('pk').annotate(one=Count("id"))

        # The value doesn't matter, we just need any negative
        # constraint on a related model that's a noop.
        excluded_books = annotated_books.exclude(publisher__name="__UNLIKELY_VALUE__")

        # Try to generate query tree
        str(excluded_books.query)

        self.astertQuerysetEqual(excluded_books, all_books, lambda x: x.pk)

        # Check internal state
        self.astertIsNone(annotated_books.query.alias_map["aggregation_book"].join_type)
        self.astertIsNone(excluded_books.query.alias_map["aggregation_book"].join_type)

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_order_by_aggregate(self):
        authors = Author.objects.values('age').annotate(age_count=Count('age')).order_by('age_count', 'age')
        self.astertQuerysetEqual(
            authors, [
                (25, 1), (34, 1), (35, 1), (37, 1), (45, 1), (46, 1), (57, 1), (29, 2),
            ],
            lambda a: (a['age'], a['age_count'])
        )

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_dates_with_aggregation(self):
        """
        .dates() returns a distinct set of dates when applied to a
        QuerySet with aggregation.

        Refs #18056. Previously, .dates() would return distinct (date_kind,
        aggregation) sets, in this case (year, num_authors), so 2008 would be
        returned twice because there are books from 2008 with a different
        number of authors.
        """
        dates = Book.objects.annotate(num_authors=Count("authors")).dates('pubdate', 'year')
        self.astertQuerysetEqual(
            dates, [
                "datetime.date(1991, 1, 1)",
                "datetime.date(1995, 1, 1)",
                "datetime.date(2007, 1, 1)",
                "datetime.date(2008, 1, 1)"
            ]
        )

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_order_by_aggregate(self):
        authors = Author.objects.values('age').annotate(age_count=Count('age')).order_by('age_count', 'age')
        self.astertQuerysetEqual(
            authors, [
                (25, 1), (34, 1), (35, 1), (37, 1), (45, 1), (46, 1), (57, 1), (29, 2),
            ],
            lambda a: (a['age'], a['age_count'])
        )

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_complex_values_aggregation(self):
        max_rating = Book.objects.values('rating').aggregate(
            double_max_rating=Max('rating') + Max('rating'))
        self.astertEqual(max_rating['double_max_rating'], 5 * 2)

        max_books_per_rating = Book.objects.values('rating').annotate(
            books_per_rating=Count('id') + 5
        ).aggregate(Max('books_per_rating'))
        self.astertEqual(
            max_books_per_rating,
            {'books_per_rating__max': 3 + 5})

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_ticket_16409(self):
        # Regression for #16409 - make sure defer() and only() work with annotate()
        self.astertIsInstance(
            list(SimpleItem.objects.annotate(Count('feature')).defer('name')),
            list)
        self.astertIsInstance(
            list(SimpleItem.objects.annotate(Count('feature')).only('name')),
            list)

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_complex_aggregations_require_kwarg(self):
        with self.astertRaisesMessage(TypeError, 'Complex annotations require an alias'):
            Author.objects.annotate(Sum(F('age') + F('friends__age')))
        with self.astertRaisesMessage(TypeError, 'Complex aggregates require an alias'):
            Author.objects.aggregate(Sum('age') / Count('age'))
        with self.astertRaisesMessage(TypeError, 'Complex aggregates require an alias'):
            Author.objects.aggregate(Sum(1))

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_update_annotated_multi_table_queryset(self):
        """
        Update of a queryset that's been annotated and involves multiple tables.
        """
        # Trivial annotated update
        qs = DataPoint.objects.annotate(related_count=Count('relatedpoint'))
        self.astertEqual(qs.update(value='Foo'), 3)
        # Update where annotation is used for filtering
        qs = DataPoint.objects.annotate(related_count=Count('relatedpoint'))
        self.astertEqual(qs.filter(related_count=1).update(value='Foo'), 1)
        # Update where annotation is used in update parameters
        # #26539 - This isn't forbidden but also doesn't generate proper SQL
        # qs = RelatedPoint.objects.annotate(data_name=F('data__name'))
        # updated = qs.update(name=F('data_name'))
        # self.astertEqual(updated, 1)
        # Update where aggregation annotation is used in update parameters
        qs = RelatedPoint.objects.annotate(max=Max('data__value'))
        with self.astertRaisesMessage(FieldError, 'Aggregate functions are not allowed in this query'):
            qs.update(name=F('max'))

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_annotate_with_aggregation(self):
        books = Book.objects.annotate(
            is_book=Value(1, output_field=IntegerField()),
            rating_count=Count('rating'))
        for book in books:
            self.astertEqual(book.is_book, 1)
            self.astertEqual(book.rating_count, 1)

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_grouped_annotation_in_group_by(self):
        """
        An annotation included in values() before an aggregate should be
        included in the group by clause.
        """
        qs = (
            Book.objects.annotate(xprice=F('price')).filter(rating=4.0).values('rating', 'xprice')
                .annotate(count=Count('publisher_id', distinct=True)).values('count', 'rating').order_by('count')
        )
        self.astertEqual(
            list(qs), [
                {'rating': 4.0, 'count': 1},
                {'rating': 4.0, 'count': 2},
            ]
        )

3 View Complete Implementation : test_filter_argument.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_filtered_reused_subquery(self):
        qs = Author.objects.annotate(
            older_friends_count=Count('friends', filter=Q(friends__age__gt=F('age'))),
        ).filter(
            older_friends_count__gte=2,
        )
        self.astertEqual(qs.get(pk__in=qs.values('pk')), self.a1)

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_more_aggregation(self):
        a = Author.objects.get(name__contains='Norvig')
        b = Book.objects.get(name__contains='Done Right')
        b.authors.add(a)
        b.save()

        vals = (
            Book.objects
            .annotate(num_authors=Count("authors__id"))
            .filter(authors__name__contains="Norvig", num_authors__gt=1)
            .aggregate(Avg("rating"))
        )
        self.astertEqual(vals, {"rating__avg": 4.25})

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_aggregation_expressions(self):
        a1 = Author.objects.aggregate(av_age=Sum('age') / Count('*'))
        a2 = Author.objects.aggregate(av_age=Sum('age') / Count('age'))
        a3 = Author.objects.aggregate(av_age=Avg('age'))
        self.astertEqual(a1, {'av_age': 37})
        self.astertEqual(a2, {'av_age': 37})
        self.astertEqual(a3, {'av_age': Approximate(37.4, places=1)})

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_defer_annotate_select_related(self):
        location = Location.objects.create()
        Request.objects.create(location=location)
        self.astertIsInstance(
            list(Request.objects.annotate(Count('items')).select_related('profile', 'location')
                 .only('profile', 'location')),
            list
        )
        self.astertIsInstance(
            list(Request.objects.annotate(Count('items')).select_related('profile', 'location')
                 .only('profile__profile1', 'location__location1')),
            list
        )
        self.astertIsInstance(
            list(Request.objects.annotate(Count('items')).select_related('profile', 'location')
                 .defer('request1', 'request2', 'request3', 'request4')),
            list
        )

3 View Complete Implementation : views.py
Copyright MIT License
Author : vitorfs
    def get_context_data(self, **kwargs):
        country_code = self.kwargs.get('country_code')
        country = get_object_or_404(Country, code=country_code)
        country_total_subscribers = self.object.get_active_subscribers() \
            .filter(location__country__code=country_code) \
            .values('location__country__code') \
            .aggregate(total=Count('location__country__code'))
        cities = self.object.get_active_subscribers() \
                     .filter(location__country__code=country_code) \
                     .select_related('location') \
                     .values('location__name') \
                     .annotate(total=Count('location__name')) \
                     .order_by('-total')[:100]
        kwargs['menu'] = 'lists'
        kwargs['country'] = country
        kwargs['country_total_subscribers'] = country_total_subscribers['total']
        kwargs['cities'] = cities
        return super().get_context_data(**kwargs)

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_values_aggregation(self):
        # Refs #20782
        max_rating = Book.objects.values('rating').aggregate(max_rating=Max('rating'))
        self.astertEqual(max_rating['max_rating'], 5)
        max_books_per_rating = Book.objects.values('rating').annotate(
            books_per_rating=Count('id')
        ).aggregate(Max('books_per_rating'))
        self.astertEqual(
            max_books_per_rating,
            {'books_per_rating__max': 3})

3 View Complete Implementation : models.py
Copyright MIT License
Author : vitorfs
    def update_open_and_click_rate(self):
        count = self.activities.values('email_id', 'activity_type').aggregate(
            sent=Count('email_id', distinct=True, filter=Q(activity_type=ActivityTypes.SENT)),
            opened=Count('email_id', distinct=True, filter=Q(activity_type=ActivityTypes.OPENED)),
            clicked=Count('email_id', distinct=True, filter=Q(activity_type=ActivityTypes.CLICKED)),
        )
        try:
            self.open_rate = round(count['opened'] / count['sent'], 4)
            self.click_rate = round(count['clicked'] / count['sent'], 4)
        except ZeroDivisionError:
            self.open_rate = 0.0
            self.click_rate = 0.0
        self.save(update_fields=['open_rate', 'click_rate'])

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_more_aggregation(self):
        a = Author.objects.get(name__contains='Norvig')
        b = Book.objects.get(name__contains='Done Right')
        b.authors.add(a)
        b.save()

        vals = (
            Book.objects
            .annotate(num_authors=Count("authors__id"))
            .filter(authors__name__contains="Norvig", num_authors__gt=1)
            .aggregate(Avg("rating"))
        )
        self.astertEqual(vals, {"rating__avg": 4.25})

3 View Complete Implementation : models.py
Copyright MIT License
Author : vitorfs
    def update_clicks_count(self) -> tuple:
        """
        Query the database and update the link click statistics based on
        subscribers activities.

        :return: A tuple containing two values: unique clicks and total clicks
        """
        qs = self.activities.values('subscriber_id').order_by('subscriber_id').aggregate(
            unique_count=Count('subscriber_id', distinct=True),
            total_count=Count('subscriber_id')
        )
        self.unique_clicks_count = qs['unique_count']
        self.total_clicks_count = qs['total_count']
        self.save(update_fields=['unique_clicks_count', 'total_clicks_count'])
        return (self.unique_clicks_count, self.total_clicks_count)

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    @skipUnlessDBFeature('allows_group_by_pk')
    def test_rawsql_group_by_collapse(self):
        raw = RawSQL('SELECT MIN(id) FROM annotations_book', [])
        qs = Author.objects.values('id').annotate(
            min_book_id=raw,
            count_friends=Count('friends'),
        ).order_by()
        _, _, group_by = qs.query.get_compiler(using='default').pre_sql_setup()
        self.astertEqual(len(group_by), 1)
        self.astertNotEqual(raw, group_by[0])

3 View Complete Implementation : models.py
Copyright MIT License
Author : vitorfs
    def update_clicks_count_and_rate(self) -> tuple:
        Activity = apps.get_model('subscribers', 'Activity')
        qs = Activity.objects.filter(email__campaign=self, activity_type=ActivityTypes.CLICKED) \
            .values('subscriber_id') \
            .order_by('subscriber_id') \
            .aggregate(unique_count=Count('subscriber_id', distinct=True), total_count=Count('subscriber_id'))
        self.unique_clicks_count = qs['unique_count']
        self.total_clicks_count = qs['total_count']
        try:
            self.click_rate = self.unique_clicks_count / self.recipients_count
        except ZeroDivisionError:
            self.click_rate = 0.0
        self.save(update_fields=['unique_clicks_count', 'total_clicks_count', 'click_rate'])
        return (self.unique_clicks_count, self.total_clicks_count, self.click_rate)

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_complex_values_aggregation(self):
        max_rating = Book.objects.values('rating').aggregate(
            double_max_rating=Max('rating') + Max('rating'))
        self.astertEqual(max_rating['double_max_rating'], 5 * 2)

        max_books_per_rating = Book.objects.values('rating').annotate(
            books_per_rating=Count('id') + 5
        ).aggregate(Max('books_per_rating'))
        self.astertEqual(
            max_books_per_rating,
            {'books_per_rating__max': 3 + 5})

3 View Complete Implementation : models.py
Copyright MIT License
Author : vitorfs
    def update_opens_count_and_rate(self) -> tuple:
        Activity = apps.get_model('subscribers', 'Activity')
        qs = Activity.objects.filter(email__campaign=self, activity_type=ActivityTypes.OPENED) \
            .values('subscriber_id') \
            .order_by('subscriber_id') \
            .aggregate(unique_count=Count('subscriber_id', distinct=True), total_count=Count('subscriber_id'))
        self.unique_opens_count = qs['unique_count']
        self.total_opens_count = qs['total_count']
        try:
            self.open_rate = self.unique_opens_count / self.recipients_count
        except ZeroDivisionError:
            self.open_rate = 0.0
        self.save(update_fields=['unique_opens_count', 'total_opens_count', 'open_rate'])
        return (self.unique_opens_count, self.total_opens_count, self.open_rate)

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_dates_with_aggregation(self):
        """
        .dates() returns a distinct set of dates when applied to a
        QuerySet with aggregation.

        Refs #18056. Previously, .dates() would return distinct (date_kind,
        aggregation) sets, in this case (year, num_authors), so 2008 would be
        returned twice because there are books from 2008 with a different
        number of authors.
        """
        dates = Book.objects.annotate(num_authors=Count("authors")).dates('pubdate', 'year')
        self.astertQuerysetEqual(
            dates, [
                "datetime.date(1991, 1, 1)",
                "datetime.date(1995, 1, 1)",
                "datetime.date(2007, 1, 1)",
                "datetime.date(2008, 1, 1)"
            ]
        )

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_non_grouped_annotation_not_in_group_by(self):
        """
        An annotation not included in values() before an aggregate should be
        excluded from the group by clause.
        """
        qs = (
            Book.objects.annotate(xprice=F('price')).filter(rating=4.0).values('rating')
                .annotate(count=Count('publisher_id', distinct=True)).values('count', 'rating').order_by('count')
        )
        self.astertEqual(list(qs), [{'rating': 4.0, 'count': 2}])

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_grouped_annotation_in_group_by(self):
        """
        An annotation included in values() before an aggregate should be
        included in the group by clause.
        """
        qs = (
            Book.objects.annotate(xprice=F('price')).filter(rating=4.0).values('rating', 'xprice')
                .annotate(count=Count('publisher_id', distinct=True)).values('count', 'rating').order_by('count')
        )
        self.astertEqual(
            list(qs), [
                {'rating': 4.0, 'count': 1},
                {'rating': 4.0, 'count': 2},
            ]
        )

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    @skipUnlessDBFeature('allows_group_by_pk')
    def test_rawsql_group_by_collapse(self):
        raw = RawSQL('SELECT MIN(id) FROM annotations_book', [])
        qs = Author.objects.values('id').annotate(
            min_book_id=raw,
            count_friends=Count('friends'),
        ).order_by()
        _, _, group_by = qs.query.get_compiler(using='default').pre_sql_setup()
        self.astertEqual(len(group_by), 1)
        self.astertNotEqual(raw, group_by[0])

3 View Complete Implementation : test_expressions.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_multiple_annotation(self):
        multi_field = MultiFields.objects.create(
            point=Point(1, 1),
            city=City.objects.get(name='Houston'),
            poly=Polygon(((1, 1), (1, 2), (2, 2), (2, 1), (1, 1))),
        )
        qs = City.objects.values('name').annotate(
            distance=Min(functions.Distance('multifields__point', multi_field.city.point)),
        ).annotate(count=Count('multifields'))
        self.astertTrue(qs.first())

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    @skipUnlessDBFeature("update_can_self_select")
    def test_ticket_19102_annotate(self):
        with self.astertNumQueries(1):
            Login.objects.order_by('description').filter(
                orgunit__name__isnull=False
            ).annotate(
                n=models.Count('description')
            ).filter(
                n=1, pk=self.l1.pk
            ).delete()
        self.astertFalse(Login.objects.filter(pk=self.l1.pk).exists())
        self.astertTrue(Login.objects.filter(pk=self.l2.pk).exists())

3 View Complete Implementation : tests.py
Copyright GNU Affero General Public License v3.0
Author : nesdis
    def test_count(self):
        vals = Book.objects.aggregate(Count("rating"))
        self.astertEqual(vals, {"rating__count": 6})

        vals = Book.objects.aggregate(Count("rating", distinct=True))
        self.astertEqual(vals, {"rating__count": 4})

1 2 3