I have an analytics dashboard - basically it shows the overview of the data's. Lets say that we have a table named "Records"

In my analytics dashboard, i need to show various details about the records of particular user:

user = Records.objects.filter(id=pk)

By this we get all the records associate with the user, now to show various analytics like as follows,

Total Records,
Total Records by Today
Total Records by Week
Total Records by Month
Total Active Records // Records which has status == active
Total InActive Records // Records which has status == inactive

How to do all these ? While researching i found few options to follow,

Option 1 : Do separate query for each of the need
Option 2 : Do fetch all the data's and perform the above calculations in view and send as context

How to deal with these ? Am also planning to use charts

Class Records:
    owner = models.ForeignKey(User, on_delete=models.CASCADE)
    name = models.CharField(max_length=120)
    code = models.CharField(max_length=16)
    type = models.CharField(max_length=32)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now = True)

CodePudding user response：

If you plan on using a frontend library like Chart.js (which I highly recommend), you have two approaches here:

1. Create the data structure for each query on the backend and serve all the data at once to the front end

   • Advantages:
     • Once the data is available in the front end, rendering scripts will run much quicker (i.e. changing charts)
   • Disadvantages
     • Data may take longer to initialize as the backend is doing a lot at once
     • The backend code may become redundant or overly complex and difficult to manage

2. Create an endpoint on the backend and query it dynamically from the front end

   • Advantages:
     • The backend is doing less work for each individual request, so load times are faster
   • Disadvantages:
     • Users who constantly flip between charts may experience lag as they wait for each request to execute

Obviously, this is not an exhaustive list of the pros and cons of each option, though generally I opt for option 2 in my projects.

CodePudding user response：

Ideally, Do database work in the database rather than in Python, unless you have budget-constraints on using the database service (though the work would then be shifted to your app worker). Since you are only interested with the number of items, you can just use QuerySet.count(). See the Field Lookups for reference of the queries made below.

from datetime import datetime, date

user_records = Records.objects.filter(owner=some_user)
today = date.today()

total_records = user_records.count()
total_records_today = user_records.filter(created_at__date=today).count()
total_records_week = user_records.filter(created_at__year=today.year, created_at__week=today.isocalendar()[1]).count()
total_records_month = user_records.filter(created_at__year=today.year, created_at__month=today.month).count()
total_records_active = user_records.filter(status="active").count()
total_records_inactive = user_records.filter(status="inactive").count()

Or you can also manually perform the tally on all of the records:

for rec in user_records:
    if rec.created_at.date() == today:
        total_records_today  = 1
    if rec.created_at.date().isocalendar()[1] == today.isocalendar()[1]:
        total_records_week  = 1
    ...

Or:

total_records_today = len(list(filter(lambda rec: rec.created_at.date() == today, user_records)))
total_records_week = len(list(filter(lambda rec: rec.created_at.date().isocalendar()[1] == today.isocalendar()[1], user_records)))
...