Django query to return percentage of a users with a post-CodePudding

Two models Users (built-in) and Posts:

class Post(models.Model):
    post_date = models.DateTimeField(default=timezone.now)
    user = models.ForeignKey(User, on_delete=models.CASCADE, null=True, related_name='user_post')
    post = models.CharField(max_length=100)

I want to have an API endpoint that returns the percentage of users that have posted. Basically I want SUM(unique users who have posted) / total_users

I have been trying to play around with annotate and aggregate, but I am getting the sum of posts for each users, or the sum of users per post (which is one...). How can I get the sum of posts returned with unique users, divide that by user.count and return?

I feel like I am missing something silly but my brain has gone to mush staring at this.

class PostParticipationAPIView(generics.ListAPIView):
    queryset = Post.objects.all()
    serializer_class = PostSerializer
            
    def get_queryset(self):
        start_date = self.request.query_params.get('start_date')
        end_date = self.request.query_params.get('end_date')
        # How can I take something like this, divide it by User.objects.all().count() * 100, and assign it to something to return as the queryset?
        queryset = Post.objects.filter(post_date__gte=start_date, post_date__lte=end_date).distinct('user').count()         
        return queryset

My goal is to end up with the endpoint like:

{ total_participation: 97.3 }

Thanks for any guidance.

BCBB

CodePudding user response：

something like this should work

# get total user count
total_users = User.objects.count()
# get unique set of users with post
total_users_who_posted = Post.objects.filter(...).distinct("user").count()
# calculate_percentage
percentage = { 
    "total_participation": (total_users_who_posted*100)/ total_users
}
# take caution of divion by zero

CodePudding user response：

I don't think it is possible to use djangos orm to do this completely but you can use the orm to get the user counts (with posts and total):

from django.db.models import BooleanField, Case, Count, When, Value

counts = (User
          .objects
          .annotate(posted=Case(When(user_post__isnull=False,
                                     then=Value(True)),
                                default=Value(False), 
                                output_field=BooleanField()))
          .values('posted')
          .aggregate(posted_users=Count('pk', filter=Q(posted=True)),
                     total_users=Count('pk', filter=Q(posted__isnull=False)))

# This will result in a dict containing the following:
# counts = {'posted_users': ...,
#           'total_users': ....}