I am writing a newsletter app for Django. The app handles subscriber enrolling, newsletter rendering and sending. I am stuck with my lack of understanding of databases with respect to the Many to Many relationship and the 'through' model.
My models.py has a subscriber model and a newsletter model and a 'through' model that binds them. There is also an email read and date_read field in the 'through', which will be updated the first time that the subscriber opens the email and allows images (not guaranteed I know).
One subscriber can have many newsletters and one newsletter can have many subscribers.
Here is an abbreviation of my models.py:
class Subscriber(models.Model):
first_name = models.CharField(max_length=30)
#etc..
newsletters = models.ManyToManyField('Newsletter', through='NewsletterEngagement')
def __str__(self):
return '%s %s %s %s %s %s' % \
(self.pk,
self.first_name,
#etc..,
)
class Newsletter(models.Model):
title = models.CharField(max_length=200)
#etc..
subscribers = models.ManyToManyField('Subscriber', through='NewsletterEngagement')
def __str__(self):
return '%s %s %s %s' % \
(self.pk,
#etc..,
)
class NewsletterEngagement(models.Model):
subscriber = models.ForeignKey(Subscriber, on_delete=models.CASCADE)
newsletter = models.ForeignKey(Newsletter, on_delete=models.CASCADE)
read = models.BooleanField(default=False)
read_date = models.DateField(null=True)
def __str__(self):
return '%s %s %s %s' % \
(self.subscriber,
self.newsletter,
self.read,
self.read_date,
)
The model seems to work quite well as follows:
>>> s1 = Subscriber.objects.get(pk=1)
>>> s2 = Subscriber.objects.get(pk=2)
>>> n1 = Newsletter.objects.get(pk=1)
>>> n2 = Newsletter.objects.get(pk=2)
>>> n3 = Newsletter.objects.get(pk=3)
>>> NewsletterEngagement(subscriber=s1, newsletter=n1).save()
>>> NewsletterEngagement(subscriber=s2, newsletter=n1).save()
>>> NewsletterEngagement(subscriber=s2, newsletter=n2).save()
And the following queries work as expected:
>>> n1.subscribers.values()
>>> <QuerySet [{'id': 1, 'first_name': 'XXXXXXS1', #etc.. }
{'id': 2, 'first_name':'XXXXXXS2', #etc..}
]>
>>> n2.subscribers.values()
>>> <QuerySet [{'id': 2, 'first_name': 'XXXXXXS2', #etc.. }
]>
>>> s1.newsletters.values()
>>> <QuerySet [{'id': 1, 'title': 'Newsletter One', #etc..}]>
>>> s2.newsletter.values()
>>> <QuerySet [{'id': 1, 'title': 'Newsletter One', #etc..},
{'id': 2, 'title': 'Newsletter Two', #etc..}
]>
My questions are:
- Is the model fit for its purpose and if not, how could I improve it?
- I don't know how to get all subscribers who have not been sent a newsletter (n3 above)?
I hope that I have made my questions clear and I look forward to your suggestions.