user3479125 user3479125 - 22 days ago 5
SQL Question

Django: best way to design one-to-many count viewing

I have 2 models A and B, and one A model may be referenced in several B modals:

class A(Model):
name = CharField(...)

class B(Model):
name = CharField(...)
a = ForeignKey(A, related_name='all_B')


In view of A model I want to show how many B objects there are.
For now I do this:

args={'a_all': A.objects.all()}
...
{% for a in a_all %}
{{a.name}} : {{ a.all_B.objects.count }} <br>
{% endofr %}


But, this will do SQL query for every A object, and it is not cool if I have many models in db tables.

So, I want to fetch all counts in only one query.
select_related in this case can't be used, becouse it works only for one-to-one nad many-to-one relations, but not for one-to-many.

Only thing thant comes to my head is to add counter field to A:

class A(Model):
name = CharField(...)
b_count = PositiveIntegerField(...)


And update it when I change relation. But it brings many work to detect all relations change if there are many views that add/delete/rewrite "a" field of "B" modal.

Answer

Try this:

a_all = A.objects.all().annotate(b_count =Count('b'))

This will add a new field b_count with every object of A. Then in your template you can do something like

 {% for a in a_all %}
   {{a.name}} : {{ a.b_count }} <br>
{% endofr %}