The first example implements Self-referential relationship:
class User(models.Model):
username = models.CharField(max_length=50, null=True)
referrer = models.ForeignKey("self", related_name='referrals', null=True, blank=True)
referrer_bonus = models.IntegerField(default=0)
referral_bonus = models.IntegerField(default=0)
The second option uses an intermediate model:
class Referral(models.Model):
referrer = models.ForeignKey(User, on_delete=models.PROTECT, related_name="referrals")
referral = models.OneToOneField(User, on_delete=models.PROTECT, related_name='referral')
referrer_bonus = models.IntegerField(default=0)
referral_bonus = models.IntegerField(default=0)
The question is that one referral can have one referrer. But a referrer can have many referrals.
What approach will be better if I need to use anotation to calculate the total bonus and the number of referrals. And which option will be optimal for performance?
There is no reason to use an intermediate model: if each User
has (at most) one referral, you only make things more complicated if you add an extra model: it would require extra queries to get the (id
of the) referral, and furthermore, to calculate the bonus would require enumerating over the items.
You can refer to the self
like you already did in the first example:
class User(models.Model):
username = models.CharField(max_length=50, null=True)
referrer = models.ForeignKey(
'self', related_name='referrals', null=True, blank=True
)
# no referrer_bonus/referral_bonus
You however don't need to store the referrer_bonus
or referral_bonus
explicitly. You can just can calculate it when necessary by annotating, like:
from django.db.models import Count
User.objects.annotate(referral_bonus=Count('referrals') * bonus_per_referral).get(
id=my_id
)
This prevents that the referral_bonus
eventually gets out of sync.