0
  • we have Project as main model, which contains 2 fields of M2M relationship.
class First(models.Model):
   first_results_M2M = models.ManyToManyField(First_Results)

class Second(models.Model):
   second_results_M2M = models.ManyToManyField(Second_Results)

class Project(models.Model):
    project_first_M2M = models.ManyToManyField(First)
    project_second_M2M = models.ManyToManyField(Second)

  • I m trying to count all the objects present in first_results_M2M of all the project_first_M2M objects within each Project object.

  • Here's the below example to count all the objects of first_results_M2M for Project object 1.

total_first_all = First_Results.objects.filter(first__project__id=1).count()

  • I want to render the total count of total_first_all and total_second_all in the template.
Project_Query = Project.objects.all()
for each_proj in Project_Query:
  print(each_proj.total_first_all) ## should print the count the `first_resuls_M2M` for each project obj.
  • Please let me know how to do achieve it in more effecient/fast way besides annotate.

annotate.total_first_all=Count('project_first_M2M__first_results_M2M')

Arbazz Hussain
  • 1,622
  • 2
  • 15
  • 41
  • You can `.annotate(...)`. – Willem Van Onsem Jan 07 '20 at 15:02
  • For the first time I tried with annotate then I realized that using annotate on multiple fields makes query extremely slow. `https://stackoverflow.com/questions/59622503/django-multiple-annotate-slows-down-the-query – Arbazz Hussain Jan 07 '20 at 15:10
  • 1
    If you annotate on multiple *different* joins you generate a large amount of JOINs (in that example a total of four), which will indeed "blow up", the solution table. But it of course does not make any sense to perform such query. If you here perform a single nested JOIN, then it results in two JOINs, and you take all records that are yielded into account. – Willem Van Onsem Jan 07 '20 at 15:14
  • is there any way to perform a single nested JOIN from django ORM or have to do it with raw query? – Arbazz Hussain Jan 07 '20 at 15:17
  • 1
    you use a `Subquery`. – Willem Van Onsem Jan 07 '20 at 15:22

1 Answers1

0

You .annotate(..) [Django-doc] your queryset, like:

from django.db.models import Count

project_query = Project.objects.annotate(
    total_first_all=Count('project_first_M2M__first_results_M2M')
)

for project in project_query:
    print(project.total_first_all)

This will not make a query per Project object, but calculate the counts for all Projects in "bulk".

For multiple ones, you can make use of subqueries to reduce the amount of nested JOIN:

from django.db.models import Count, OuterRef, Subquery

project_query = Project.objects.annotate(
    total_first_all=Subquery(
        First_Results.objects.filter(first__project=OuterRef('pk')).values('first__project').values(cnt=Count('*')).order_by('first__project')
    ),
    total_second_all=Subquery(
        Second_Results.objects.filter(second__project=OuterRef('pk')).values('second__project').values(cnt=Count('*')).order_by('second__project')
    )
)
Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555