Django ORM distinct values by determined date having a MySQL DB

Question

I want to read some logs of database with Django but I been having problems.

My objective is to count how many users made requests in each day in the last 7 days.

I've tried this by

logs_seven_days = Log.objects.filter( created_at__gte=datetime.now()-timedelta(days=7))\
                                        .extra({'date_of_request' : "date(created_at)"}) \
                                        .values('date_of_request') \
                                        .values('api_key__name') \
                                        .distinct() \
                                        .annotate(request_of_this_date_count=Count('id'))

I was wanting something like this:

distinct_user_requests        date
  4                         2/21/2020
  21                        2/22/2020
  5                         2/23/2020
  0                         2/24/2020
  43                        2/25/2020
  22                        2/26/2020

Answer 1

You may use following query

logs_seven_days = Log.objects.filter(
        created_at__gte = timezone.now()-timedelta(days=7) #Filter Records
    ).annotate(
        date_of_request = TruncDate('created_at') #Annotate to trunc created_at to only date part
    ).values(
        'date_of_request' #Force to group by date truncated created_at_date
    ).annotate(
        distinct_user_requests = Count('user',distinct=True) #Use aggregate Count function over 'user' field using distinct.   
    ).values(
        'distinct_user_requests','date_of_request' #Get only count and create date values 
    ).order_by(
        'date_of_request' #And you may also order by annotated field value
    )

Please, see Django docts for more info.