CODEWITHSUNDEEP
pythondjango
sinθ
sinθ

Reputation: 11493

How to keep track of how many foreign keys are connected to an object?

I have two tables in a database (that I've created): Entry and Nation. Every time a new entry is created, the user has to fill in what Nation it relates to. So an entry has two fields: Content [a charfield] and Nation [a foreign key to a Nation]. The thing is, on the homepage of the site, the most popular nations are displayed in order of how many entries they have. It seems inefficient to constantly look up how many foreign keys in the Entry table relate to each Nation, so I'm wondering how I can keep a counter. By this I mean, if I create an extra integer field for the Nations, how could I make it so that every time an entry is created, it increments the correct nation by 1, and every time an entry is destroyed, it decreases the correct nation by 1.

Upvotes: 2

Views: 83

Answers (2)

juliomalegria
juliomalegria

Reputation: 24921

You could use the post_save signal, which is sent at the end of the save() method.

First, add (as you said) a counter = IntegerField(default=0) to your Nation model.

Then, everytime a new Entry is created, increase that counter.

from django.db.models.signals import post_save

# Your models ...

def increase_nation_counter(instance, created, **kwargs):
    if created: # Just increase counter if it's a new Entry
        instance.nation.counter += 1
        instance.nation.save()

post_save.connect(increase_nation_counter, sender=Entry)

Then, you could sort your Nations with a simple query:

Nation.objects.order_by('-counter')

Upvotes: 2

acjay
acjay

Reputation: 36551

You could use aggregation. That way, your schema stays normalized, and you don't have the possibility of your data being inconsistent. Your problem seems equivalent to one of the examples quoted there. Try this:

from django.db.models import Count
Nation.objects.annotate(num_entries=Count('entry')).order_by('-num_entries')

Upvotes: 2

Related Questions