PROJECTS NOTES HOME

Paginating results in django

https://docs.djangoproject.com/en/5.0/topics/pagination/

1 With smaller amount of queries, in 1.49ms

inefficient_query2.png

2 This is my current query 250-300ms:

$("#data_table").DataTable({
    paging: true, // Pagination
    pageLength: 7, // Data per page
    bInfo: false, // Info on footer
    searching: true, // Input search
    bSort: true, // Filter A to Z, Z to A (and numbers)
    columnDefs: [
        { targets: [0, 1], orderable: true }, // Define columns to be sortable
        { targets: "_all", orderable: false }, // Disable sorting for all other columns
    ],
    order: [[0, "desc"]], // Set default sorting to the first column (date) in descending order
});
@login_required
def data_table(request):
    answers = Answer.objects.filter(created_by=request.user)
    questions = Question.objects.filter(created_by=request.user)
    rescuetime_entries = Rescuetime.objects.filter(user=request.user)
    weather_entries = Weather.objects.all()

    data_to_display = []

    for weather_entry in weather_entries:
        matching_answers = []
        productive_hours = None
        distracting_hours = None

        for question in questions:
            corresponding_answer = question.answer_set.filter(date_added__date=weather_entry.date).first()
            if corresponding_answer:
                matching_answers.append({
                    'description': question.description,
                    'answer': corresponding_answer.answer,
                })
            else:
            # If there's no corresponding answer, add an empty answer
                matching_answers.append({
                'description': question.description,
                'answer': '',
            })

        # Find the matching Rescuetime entry for the weather entry's date
        for rescuetime_entry in rescuetime_entries:
            if rescuetime_entry.date == weather_entry.date:
                productive_hours = rescuetime_entry.productive_hours
                distracting_hours = rescuetime_entry.distracting_hours
                break

        if matching_answers:
            data_to_display.append({
                'date': weather_entry.date,
                'temperature': weather_entry.temperature,
                'answers': matching_answers,
                'productive_hours': productive_hours,
                'distracting_hours': distracting_hours,

            })

    context = {
        'user': request.user,
        'answers': Answer.objects.filter(created_by=request.user),
        'data_to_display': data_to_display,
        'questions': questions,
    }

    return render(request, 'data_table.html', context)

This results in such time. Aroun 250-300ms.

inefficient_query1.png

Now I am using data tables - https://datatables.net/

But I think Django still makes ALL the possible calls, to fetch 2000 records of data as you can see in the screenshot and data tables simply shape the table to have 'pagination'.

inefficient_query3.png

3 After applying pagination like such:

@login_required
def data_table(request):
    answers = Answer.objects.filter(created_by=request.user)
    questions = Question.objects.filter(created_by=request.user)
    rescuetime_entries = Rescuetime.objects.filter(user=request.user)
    weather_entries = Weather.objects.all()

    data_to_display = []

    for weather_entry in weather_entries:
        matching_answers = []
        productive_hours = None
        distracting_hours = None

        for question in questions:
            corresponding_answer = question.answer_set.filter(date_added__date=weather_entry.date).first()
            if corresponding_answer:
                matching_answers.append({
                    'description': question.description,
                    'answer': corresponding_answer.answer,
                })
            else:
            # If there's no corresponding answer, add an empty answer
                matching_answers.append({
                'description': question.description,
                'answer': '',
            })

        # Find the matching Rescuetime entry for the weather entry's date
        for rescuetime_entry in rescuetime_entries:
            if rescuetime_entry.date == weather_entry.date:
                productive_hours = rescuetime_entry.productive_hours
                distracting_hours = rescuetime_entry.distracting_hours
                break

        if matching_answers:
            data_to_display.append({
                'date': weather_entry.date,
                'temperature': weather_entry.temperature,
                'answers': matching_answers,
                'productive_hours': productive_hours,
                'distracting_hours': distracting_hours,

            })



    # Pagination START

    from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger

    page = request.GET.get('page')
    paginator = Paginator(data_to_display, 5)  # You can change the number of items per page (e.g., 10)

    try:
        data_to_display = paginator.page(page)
    except PageNotAnInteger:
        data_to_display = paginator.page(1)
    except EmptyPage:
        data_to_display = paginator.page(paginator.num_pages)

    # Pagination END

    context = {
        'user': request.user,
        'answers': Answer.objects.filter(created_by=request.user),
        'data_to_display': data_to_display,
        'questions': questions,
    }

    return render(request, 'data_table.html', context)

Then add this to html and you are done.

<div class="pagination">
    <span class="step-links">
        {% if data_to_display.has_previous %}
        <a href="?page=1">&laquo; first</a>
        <a href="?page={{ data_to_display.previous_page_number }}">previous</a>
        {% endif %}

        <span class="current-page">
            Page {{ data_to_display.number }} of {{ data_to_display.paginator.num_pages }}.
        </span>

        {% if data_to_display.has_next %}
        <a href="?page={{ data_to_display.next_page_number }}">next</a>
        <a href="?page={{ data_to_display.paginator.num_pages }}">last &raquo;</a>
        {% endif %}
    </span>
</div>

4 BUT

The pagination itself may not significantly reduce the number of queries, but it helps in breaking the results into multiple pages, which can improve the user experience by not loading all data at once.

I have noticed that.

5 Probably it's a good idea to look into the database, pagination itself won't help

Now that I know how pagination works and what it does, let's try to fix this problem another way - optimizing database queries