Keep track of follow/unfollow events django

Question

I am building a sort of social media app, where users can follow certain pages. I want the page managers to be able to visualize their followers in a chart for each day/week or so. So I am thinking about how to implement the following system. For now, I have this model:

class FollowHistory(models.Model):
    user = models.ForeignKey('User', on_delete=models.CASCADE, related_name="follow_history")
    page = models.ForeignKey('Page', on_delete=models.CASCADE, related_name="follower_history")
    timestamp = models.DateTimeField(auto_now_add=True)
    followed = models.BooleanField()

When a user clicks Follow, I create an instance with followed = True, and the timestamp of the event. When a user clicks Unfollow, I do the same, but with followed = False.

I am struggling to figure out how to query the data in a way for a page to see how many followers they had each day. So far I have tried this:

FollowHistory.objects.filter(page=page, followed=True)\
.annotate(ts_day=Trunc('timestamp', 'day'))\
.values('ts_day')\
.annotate(followers=Count('id'))

But that just gives me the count of events that happened in a day, so if I follow and unfollow a company 10 times, then it will count 10 times. I only need to know by the end of the day, did I follow or not?

Maybe there is a better implementation of the model that you could recommend, I am open to that too. Thanks!

Update:

I managed to set up a query that outputs the change in number of followers each day:

FollowHistory.objects.filter(page=page)\
.values('timestamp')\
.annotate(followings=(Count(Case(When(followed=True, then=1)))-Count(Case(When(followed=False, then=1)))))\
.order_by('timestamp')

Where followings calculates the difference between follows and unfollows for each day.

The result is something like this:

date	followings
28.02.2022	1
01.03.2022	3
04.03.2022	-1
05.03.2022	2
08.03.2022	0

So there are still a few problems with this:

1. I need the cumulative sum, so a column that goes 1, 4, 3, 5, 5
2. How would I fill in the missing dates when no one follows or unfollows?
3. On the 8th of march, followings equals 0 because someone followed and later unfollowed a page.

I don't know if I should stick with this approach or just find another way to set up the FollowHistory model?

Answer 1

create two queries:

people_who_unfollowed = FollowHistory.objects.filter(timestamp__year=2022, timestamp__month=03, timestamp__day=12,page=page, followed=False)
people_who_followed   = FollowHistory.objects.filter(timestamp__year=2022, timestamp__month=03, timestamp__day=12,page=page, followed=True)

now your output for 12 march 2022 will be:

followings = len(people_who_followed) - len(people_who_unfollowed)

or there is another approach:

class FollowHistory(models.Model):
   user = models.ForeignKey('User', on_delete=models.CASCADE, related_name="follow_history")
   page = models.ForeignKey('Page', on_delete=models.CASCADE, related_name="follower_history")
   timestamp = models.DateField(auto_now_add=True)
   counter = models.IntegerField()

when any user wants to follow or unfollow:

today = date.today()
get_or_create_Follow_history_object, created = FollowHistory.objects.get_or_create(timestamp__year=today.year, timestamp__month=today.monnh, timestamp__day=today.day,page=page)
if created == True:
    # here is some logic with created FollowHistory object
    # you need to up or down counter of this model
    # get_or_create_Follow_history_object.counter = get_or_create_Follow_history_object.counter + 1 #(or-1 if unfollow)
    # get_or_create_Follow_history_object.save()
else:
    # this is for object which already exists
    # get_or_create_Follow_history_object.counter = get_or_create_Follow_history_object.counter + 1 #(or-1 if unfollow)
    # get_or_create_Follow_history_object.save()
   

this will get FollowHistory if it is exists and created variable will be False,

if it doesnt exist, it will be created, and created variable will be True

at the end of the day you will have many FollowHistory objects wich will correspond on how many follows/unfollows happend (.counter property) and its date, so you can make a query:

all_follow_history_objects = FollowHistory.objects.filter(page=page, user=user)

and send it to your html page, where you can output all data in table in for loop

{% for i in all_follow_history_objects %}
    <p>date: {{i.timestamp}} ---  counter: {{i.counter}}</p>
{% endfor %}