user3479125 user3479125 - 9 months ago 38
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.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 Source

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.b_count }} <br>
{% endofr %}