Aggregate number of likes for each day within period

Question

I am building a REST Api on Django RF which represents a very basic social network. I have a model Post that has a value Likes which is related to a User by many to many through a Like model. My models:

class Post(models.Model):
    content = models.TextField()
    created_on = models.DateTimeField(auto_now_add=True)
    author = models.ForeignKey(CustomUser, on_delete=models.CASCADE,
                               related_name='posts')
    likes = models.ManyToManyField(CustomUser,
                                   related_name='likes',
                                   through='Like')

class Like(models.Model):
    author = models.ForeignKey(CustomUser, on_delete=models.CASCADE)
    post = models.ForeignKey(Post, on_delete=models.CASCADE)
    liked_on = models.DateTimeField(auto_now_add=True)

I need to aggregate a likes statistics for each day within a given period. For example, a request looks like this analytics/?date_from=2020-05-15&date_to=2020-05-17

And I need give something like this:

[{
    'date': 2020-05-15,
    'likes': 5,
}, {
    'date': 2020-05-16,
    'likes': 7,
}, {
    'date': 2020-05-17,
    'likes': 10,
}]

How can I do that in Django? Should I query a Post model that has likes as just a list? Or Likes model? And what query should be?

Ken4scholars · Accepted Answer · 2020-06-01T07:25:36.093

You should use Django-Filter. Then in the filter class, add the following filters:

date_from = filters.DateTimeFilter(field_name='liked_on', lookup_expr='gte')
date_to = filters.DateTimeFilter(field_name='liked_on', lookup_expr='lte')

Then you can make your request as you specified but used a datetime and not date. The filter class can be added to the LikesView.

UPDATE: Aggregation

To add aggregation, do this in the view after calling self.filter_queryset():

Like.objects.extra(select={'date': "date(<app_name>_like.liked_on)"}).annotate(likes=Count('pk')).order_by('-liked_on')

Don't forget to replace <app_name> with the name of your app and import django.db.models.Count.

This was inspired by the answers to this question.

Now you can have a serializer with the fields date and likes:

class LikeByDaySerializer(serializers.ModelSerializer):
    date = serilaizers.DateTimeField()
    likes = serializers.IntegerField()

    class Meta:
        model = Like
        fields = ('date', 'likes')

Hi, thank for your comment! It does work but the output is all the objects within a date range. Do you know how I can customise the output and aggregate objects by each day? Like: 'date': 2020-05-17, 'likes': 10 — Константин Манойло, May 31 '20 at 15:26
If it solved your problem, don't forget to accept the answer — Ken4scholars, Jun 01 '20 at 16:33

Aggregate number of likes for each day within period

1 Answers1