Django Querys - Filter ForeignKey related objects


#1

Hello everybody, this is my first question in this forum. =)

I’m doing my first Django Project, and can’t figure out how to send or get a specific set of objects on my template.

I need to get this object on the template, but in a way that I can separate the objects for each User:

on views:

...
objects = Shift.objects.all().filter(date__gte=df).filter(date__lte=dt)
...

I can get all objects for each user, but no the “filtered” ones:

That’s what I a doing on the template:

...

{% for user in users %}
                <div class="row">
                    <div class="col-md-2"> <strong>{{user.username}}</strong> </div>
                    <div class="col-md-2"> <strong> In/Out </strong> </div>
                    <div class="col-md-2"> <strong> Break </strong>  </div>
                    <div class="col-md-3"> <strong> Total </strong>  </div>
                    <div class="col-md-3"> </div>
                </div>
                
                <hr> 
                {% for i in user.shift_set.all %} # !!! Here is where I would like to get the “filtered” object.  !!!

                    <div class="row">
                        <div class="col-md-2"> {{ i.date|date:"d/m/y" }} </div>
                        <div class="col-md-2"> {{ i.clock_in|date:"H:i" }} - {{ i.clock_out|date:"H:i"}} </div>
                        <div class="col-md-2"> {{ i.break_time}} </div>
                        <div class="col-md-3"> {{i.duration}}h </div>
                        <div class="col-md-3"> <a class="btn btn-outline-danger btn-sm" href="{% url 'remove' pk=i.id %}">Remove</a> </div>
                    </div>
                    <hr>    

                {% endfor %}
...

The git repo for the project//app:

Thank you all in advance.


(Vitor Freitas) #2

In your case you can take advantage of Python’s dynamic typing and do something like this in your views.py:

def selectDate(request):
    users = User.objects.all()

    if request.method == 'POST':
        selec_date_form = forms.SelectDateForm(request.POST)

        if selec_date_form.is_valid():
            df = selec_date_form.cleaned_data['date_from']
            dt = selec_date_form.cleaned_data['date_to']

            for user in users:
                user.filtered_shifts = user.shift_set.filter(date__gte=df).filter(date__lte=dt)
    
    # rest of your code here        

    return render(request, 'hoursCalc/select.html', context)

So basically you are adding a dynamic property filtered_shifts to each user instance

In your template:

{% for user in users %}
    <div class="row">
        <div class="col-md-2"> <strong>{{user.username}}</strong> </div>
        <div class="col-md-2"> <strong> In/Out </strong> </div>
        <div class="col-md-2"> <strong> Break </strong>  </div>
        <div class="col-md-3"> <strong> Total </strong>  </div>
        <div class="col-md-3"> </div>
    </div>
    <hr> 
    {% for i in user.filtered_shifts %}
        <div class="row">
            <div class="col-md-2"> {{ i.date|date:"d/m/y" }} </div>
            <div class="col-md-2"> {{ i.clock_in|date:"H:i" }} - {{ i.clock_out|date:"H:i"}} </div>
            <div class="col-md-2"> {{ i.break_time}} </div>
            <div class="col-md-3"> {{i.duration}}h </div>
            <div class="col-md-3"> <a class="btn btn-outline-danger btn-sm" href="{% url 'remove' pk=i.id %}">Remove</a> </div>
        </div>
        <hr>
    {% endfor %}
{% endfor %}

But keep in mind this solution may be slow for large datasets, because we are executing an additional SQL inside a for loop. But for small pages this is fine. There is no need to optimise. Better to keep it simple


(Vitor Freitas) #3

The other option, where you could do the job in a single SQL query would be to re-arrange a little bit the way you are retrieving the data. So instead of first querying the users for then retrieving the shifts (which is the logical order, because that’s how you are rendering in the page), you could get first the filtered shifts then you get the users. Something like that:

views.py

def selectDate(request):
    if request.method == 'POST':
        selec_date_form = forms.SelectDateForm(request.POST)

        if selec_date_form.is_valid():
            df = selec_date_form.cleaned_data['date_from']
            dt = selec_date_form.cleaned_data['date_to']

            shifts = Shift.objects.select_related('user').filter(date__gte=df, date__lte=dt).order_by('user')
    
    # rest of your code here        

    context = {
        # other context data
        'shifts': shifts
    }
    return render(request, 'hoursCalc/select.html', context)

The select_related will pre fetch all user data so no additional query is needed, and the order_by('user') is super important for this to work! Because now we are going to regroup the data in the template:

hoursCalc/select.html

{% regroup shifts by user as user_list %}

{% for user in user_list %}
    <div class="row">
        <div class="col-md-2"> <strong>{{ user.grouper.username }}</strong> </div>
        <div class="col-md-2"> <strong> In/Out </strong> </div>
        <div class="col-md-2"> <strong> Break </strong>  </div>
        <div class="col-md-3"> <strong> Total </strong>  </div>
        <div class="col-md-3"> </div>
    </div>
    <hr>
    {% for shift in user.list %}
        <div class="row">
            <div class="col-md-2"> {{ shift.date|date:"d/m/y" }} </div>
            <div class="col-md-2"> {{ shift.clock_in|date:"H:i" }} - {{ shift.clock_out|date:"H:i" }} </div>
            <div class="col-md-2"> {{ shift.name }} </div>
            <div class="col-md-3"> {{ shift.number }}h </div>
        </div>
        <hr>
    {% endfor %}
{% endfor %}

So basically you have a list of shifts, but then we are sort of grouping by user, and rendering all shifts per users. All that with a single database query.

You can read more about the regroup template tag on the official documentation.


#4

Thank you so much for your answer Vitor! That was exactly what I needed.

I used the second solution to solve the problem, and will try to use the first option to make a variable that sum all the duration fields of each Shift instance for each user on the filtered query.


(Vitor Freitas) #5

You can also try do it on the database level using annotate and the Sum query expression :+1:


#6

Thanks so much Victor! I tried annotate(Sum) in all the ways possible but I still can’t send the values and display them in to the template:

on the views.py:

total_user_hours = User.objects.annotate(total_duration=Sum('shift__duration'))

context = {
    # other context data
    'total_user_hours':total_user_hours,
}



-Here’s how that part of the app look (The “Total” cell is where the problem is) :


I manage to get the QuerySet and individual values on the template using the context tag and doing a “for loop”:

{{total_user_hours}}
{% for i in total_user_hours %}
    {{ i }}
    {{i.total_duration}}
    {% endfor %}

# Results:  <QuerySet[<User:Joyce>,<User:Teo>]> Joyce  20.6  Teo 51.7

But the problem is: How to get only the value I need to display on the “Total” cell at the end of each user’s shift table?

Since the row that has the “Total” tag is inside other forloop, if I wrap the row with the “for loop” above, it will duplicate the row for each User, and also, will not necessarely match the values with actual table it is referring to.

Here’s the code for the tables display:

<div class="container">  
    {% regroup shifts by name as user_list %}

   
        {% for user in user_list %}
            <div class="row">
                <div class="col-md-2"> <strong>{{ user.grouper.username }}</strong> </div>
                <div class="col-md-2"> <strong> In/Out </strong> </div>
                <div class="col-md-2"> <strong> Break </strong>  </div>
                <div class="col-md-3"> <strong> Hours </strong>  </div>
                <div class="col-md-3"> {{shifts.total_duration}}  </div>
            </div>
            <hr>
            {% for shift in user.list %}
                <div class="row">
                    <div class="col-md-2"> {{ shift.date|date:"d/m/y" }} </div>
                    <div class="col-md-2"> {{ shift.clock_in|date:"H:i" }} - {{ shift.clock_out|date:"H:i" }} </div>
                    <div class="col-md-2"> {{ shift.break_time }} </div>
                    <div class="col-md-3"> {{ shift.duration }}h </div>
                </div>
                <hr>
            {% endfor %}
            
            <div class="row">
                <div class="col-md-2"> </div>
                <div class="col-md-2"> </div>
                <div class="col-md-2"> </div>
                <div class="col-md-3"> <strong>Total:</strong> {{HERE IS THE PROBLEM}} </div>
                <div class="col-md-3"> </div>
                
            </div>
            <hr>
            
        {% endfor %} 
    </div>

The second problem would be how to join annotate(Sum) with the filters, so I can display the Sum(duration) for only the shifts on the selected period, I tried something like this:

shifts = Shift.objects.select_related('name').filter(date__gte=df, date__lte=dt).annotate(total_duration=Sum('duration').order_by('name')

But I noticed that query is “calculating” the duration for each shift. Not the sum of all shifts.

I’m really lost here, I’m watching and reading tutorals and the documentation like crazy, but can’t find the answer…


(Vitor Freitas) #7

I think now the best option would be to create custom template tags.

So you can do something like:

<div class="col-md-3"> <strong>Total:</strong> {% calc_total_duration user.list %} </div>

Then in your template tags:

RotaProj/hoursCalc/templatetags/shifts.py

from django import template

register = template.Library()

@register.simple_tag
def calc_total_duration(shifts):  # here 'shifts' is the 'user.list' in the template
    total = 0.0
    for shift in shifts:
        total += shift.duration
    return total

Note that to load this calc_total_duration template tag, you will need to add this line in your template:

{% load shifts %}

Here shifts refer to the file name shifts.py inside your templatetags folder. When you create a new template tag remember to fully restart your project to make sure Django discover it.


#8

Thank you for the answer Victor, it worked perfectly! That was exaclty what I needed.