Django count visits on a date from datetime field for a particular user

Question

I have a table that looks like :

  +-------+---------+---------------------+-------------+-----------+-----------------+
    | id    | user_id | visit_time          | action      | user_type | erp_customer_id |
    +-------+---------+---------------------+-------------+-----------+-----------------+
    | 17460 |     818 | 2017-05-15 15:02:13 | NULL        | customer  |             932 |
    | 17459 |     818 | 2017-05-15 15:02:11 | NULL        | customer  |             932 |
    | 17458 |     818 | 2017-05-15 15:01:56 | NULL        | customer  |             932 |
    | 17457 |     818 | 2017-05-15 15:01:55 | NULL        | customer  |             932 |
    | 17456 |     818 | 2017-05-15 15:01:47 | NULL        | customer  |             932 |
    | 17455 |     818 | 2017-05-15 15:01:15 | NULL        | customer  |             932 |
    | 17454 |     818 | 2017-05-15 15:00:44 | NULL        | customer  |             932 |
    | 17453 |     818 | 2017-05-15 14:59:58 | NULL        | customer  |             932 |
    | 17452 |     818 | 2017-05-15 14:59:55 | NULL        | customer  |             932 |
    | 17451 |     818 | 2017-05-15 14:59:55 | NULL        | customer  |             932 |
    | 17450 |     818 | 2017-05-15 14:59:52 | NULL        | customer  |             932 |
    | 17449 |     817 | 2017-05-15 14:55:46 | NULL        | customer  |             931 |
    | 17448 |     817 | 2017-05-15 14:55:45 | NULL        | customer  |             931 |
    | 17447 |     817 | 2017-05-15 14:53:55 | NULL        | customer  |             931 |
    | 17446 |     817 | 2017-05-15 14:53:54 | NULL        | customer  |             931 |
    | 17445 |     817 | 2017-05-15 14:53:26 | NULL        | customer  |             931 |

user_id is a foreign key , visit_time is the timestamp of visit to a page.

The idea is to find the number of visits of each user for a day. A separate row should be returned for each day for each user.

So it should look like:

{'user' : 818 , 'day' : 2017-05-15 , 'visits' : 12}
{'user' : 817 , 'day' : 2017-05-15 , 'visits' : 5 }

I am trying to construct the query using Django ORM.

Here's the model definition:

class Log(models.Model):
    class Meta:
        db_table = "users_log"
        managed = False
    user = models.ForeignKey(LoginCustomer , db_column = "user_id")
    customer = models.ForeignKey(Customer , db_column = "erp_customer_id")
    visit_time = models.DateTimeField()
    user_type = models.CharField(max_length = 25)

I have been able to extract date from visit_time using:

Log.objects.annotate(day = RawSQL('DATE(visit_time)',[]))

How can i get desired data using Django ORM?

Database being used is MySQL.

What version of django are you using? Have you seen this link? http://stackoverflow.com/questions/8746014/django-group-by-date-day-month-year — Colwin, May 15 '17 at 11:15
I'm using Django 1.11. The answer in there seems to work except the `TruncDate` part(returns day as None). I used `RawSQL(DATE(visit_time))` instead and it worked. Thanks. — xssChauhan, May 15 '17 at 11:25

score 2 · Accepted Answer · answered May 15 '17 at 11:35

2

You need to use

from django.db.models import Count
from django.db.models.functions import TruncDay
Log.objects
.annotate(period=TruncDay('visit_time'))
.values('period', 'user_id')
.annotate(visits=Count('id'))
.values('period', 'visits')

answered May 15 '17 at 11:35

Agam Banga

2,708
1
11
18

Just for clarity : Shouldn't `visits = Count('user_id')` be used to count the number of visits? – xssChauhan May 15 '17 at 11:54
Also, `queryset.values()` returns dictionary instances that cannot be used in django model admin. Any idea how i can use the result of this queryset in admin? – xssChauhan May 15 '17 at 12:04

Django count visits on a date from datetime field for a particular user

1 Answers1