Django: count every same value in a object.filter-CodePudding

I have a CourseEmailPersonEven model that has this:

class CourseEmailPersonEvent(models.Model):
uid = models.UUIDField(primary_key=True, default=uuid.uuid4)

action = models.CharField(max_length=32)
is_filtered = models.BooleanField(default=False)

user_agent = models.CharField(max_length=255, null=True, blank=True)
ip = models.GenericIPAddressField(null=True, blank=True)
ip_address = models.ForeignKey(Ip, on_delete=models.SET_NULL, null=True, blank=True)

created_at = models.DateTimeField(auto_now_add=True)
course_email_person = models.ForeignKey(
    CourseEmailPerson, related_name="events", on_delete=models.CASCADE
)

In a viewset (retrieve), i'm getting CourseEmailPersonEvent's informations about one single account. But I wanna add the amount of same "ip_address" used from every accounts (even mine). And I don't know what to do:

class UtilsViewSet(viewsets.ViewSet):

@action(detail=False, methods=["get"], url_path="emails/events/ips")
def emails_events_ip(self, request, *args, **kwargs):

    account = get_object_or_404(models.Account, uid=self.kwargs["account_pk"])

    ips_events_count = (
        models.CourseEmailPersonEvent.objects.filter(
            course_email_person__account=account,
            action="open",
            ip_address__isnull=False,
        )
        .values("ip_address")
        .annotate(Count("ip_address"))
        .order_by("-ip_address__count")
    )

    return Response(ips_events_count)

Here the result I wanna have in final: (to add that "ip_address_used_all_account" line)

[
{
    "ip_address": "4ead446d-28c5-4641-b44d-f1e3aa7d26f8",
    "ip_address__count": 1,
    "ip_address_used_all_account: 2,
}
]

CodePudding user response：

How large is this table? How heavily used is this view?

The brute-force approach is that once you have established which ip object you are interested in, do a separate query to count the uses. If I've understood your model right,

ip_address_used_all_account = CourseEmailPersonEvent.filter( ip_address = ip 
    ).count()

Unless there are obvious performance issues with this approach, just keep it simple!

CodePudding user response：

Try this:

class UtilsViewSet(viewsets.ViewSet):
   @action(detail=False, methods=["get"], url_path="emails/events/ips")
   def emails_events_ip(self, request, *args, **kwargs):
      account = get_object_or_404(models.Account, uid=self.kwargs["account_pk"])
      ips = models.CourseEmailPersonEvent.objects.filter(course_email_person__account=account, action="open", ip_address__isnull=False).values_list("ip_address")
      ips_events_count = models.CourseEmailPersonEvent.objects.filter(ip_address__in=ips).values('ip_address').annotate(Count('ip_address'))
      return Response(ips_events_count)