How to get count of foreign key relationship for each instance in queryset?-CodePudding

I have the following Models:

# app_a/models.py

class Poller(models.Model):
    poller_id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    created_on = models.DateTimeField(auto_now_add=True)

# app_b/models.py

class PollerComment(models.Model):
    poller = models.ForeignKey(Poller, on_delete=models.CASCADE, related_name='PollerComment')
    user = models.ForeignKey(Account, on_delete=models.CASCADE)

And this view to render the template:

def render_pollboard(request):

    # Query Pollers
    poller_queryset = Poller.objects.all()

    # Convert to list
    qs_list = list(poller_queryset)

    # Shuffle the list
    shuffle(qs_list)

    # Retrieve comment count per Poller
    comments_qs = PollerComment.objects.filter(poller_id=poller.poller_id)
[..]

In the view I try to get the comment count for each Poller in poller_queryset. How to do this?

Side note: I tried to implement the comment count as a method to the Poller model, but due to my design this leads into a circular import error of module of app_b/models.py

CodePudding user response：

you can use the poller instance to get the count of its foreign keys

for poll in poller_queryset:
    poll.PollerComment_set.count() // gives you the count of comment for that poll

also you can use Count

from django.db.models import Count
Poller.objects.annotate(
    ncomments=Count(PollerComment)
)

CodePudding user response：

You can work with a .annotate(…) [Django-doc] here:

from django.db.models import Count

Poller.objects.annotate(
    ncomments=Count('PollerComment')
)

Poller objects that arise from this queryset will have an extra attribute .ncomments with the number of related PollerComments.

If you thus use this as the poller_queryset, you can use this in your template as:

{% for poller in qs_list %}
    {{ poller.poller_id }}: {{ poller.ncomments }}
{% endfor %}