Django: count every same value in a object.filter
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,
}
]
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!
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)