0

I have Unit in my app that creating Orders and Reports each week, I want view that results as table containing name of Units in first column and 3 newest Orders and Reports in next 6 column. so i need query of Units and 3 of related Order and Report to each Unit. I try to use prefetch_related in this way but don't know how to limit it (and even make it work)

Unit.objects.filter(parent__name__contains="x").prefetch_related('order_set', 'reportfrom_set').order_by('order__date','reportfrom__submit_datetime')

Thanks in advance

Bheid
  • 306
  • 3
  • 11

1 Answers1

0

The prefetch_related() method will execute a database query for each lookup you want to prefetch. Basically, here, Django will execute a first database query to get your Unit instances and then two other queries to get the Order and Report instances.

So, once your Unit.obects.filter(...).prefetch_related(...) queryset is executed, each Unit instance will have its related Order and Report objects cached (no more database queries required to fetch these data).

views.py

Since Django 1.7, the prefetch_related() method accepts a Prefetch object. This Prefetch object has a queryset attribute that helps you to filter/order the related objects:

from django.shortcuts import render_to_response

def my_view(request):
    units = Unit.objects.all().prefetch_related(
        Prefetch(
            'orders_set',
            queryset=Order.objects.all().order_by('date'),
            to_attr='prefetched_orders'
        ),
        Prefetch(
            'report_set',
            queryset=Report.objects.all().order_by('submit_datetime'),
            to_attr='prefetched_reports'
        )
    )

    for unit in units:
        unit.prefetched_orders = unit.prefetched_orders[:3]
        unit.prefetched_reports = unit.prefetched_reports[:3]

    return render_to_response('template.html', context={
        'units': units
    })

The to_attr attribute let us store the related objects in a Python list. So, slicing those lists will not call the database anymore.

Now you have your newest orders and reports and you can build the object you want.

template.html

You can iterate over your units like that (the units collection being the template context's units object):

{%  for unit in units %}
    ...
{% endfor %}

and then, you can browse the previously prefetched objects (tabular design with this code):

<table>
{%  for unit in units %}
    <tr>
        <td>{{ unit.field_to_display }}</td>
        {% for order in unit.prefetched_orders %}
            <td>{{ order.field_to_display}}</td>
        {% endfor %}

        {% for report in unit.prefetched_reports %}
            <td>{{ report.field_to_display}}</td>
        {% endfor %}
    </tr>
{% endfor %}
</table>

More info about the templating language can be found in the official doc.

Nicolas W.
  • 642
  • 6
  • 20
  • unfortunately, slicing that queries will perform another query. – GwynBleidD Oct 23 '16 at 20:41
  • Then, we can take advantage of the to_attr attribute of the Prefetch object, to assign the queryset result to a list. Splicing the list should then be ok, no? – Nicolas W. Oct 23 '16 at 20:45
  • That's the main problem, splicing that list for every `Unit` model. – GwynBleidD Oct 23 '16 at 20:54
  • Thanks Nicolas and @GwynBleidD. Seems OK but I'm afraid of performance lake. please explain about number of more queries this answer perform. Is it order of Unit numbers? – Bheid Oct 24 '16 at 06:12
  • EDIT: and please explain how to access related Orders and Reports in templates? – Bheid Oct 24 '16 at 06:32
  • @BehnamHeydari post updated. I added a (basic) template example and the to_attr usage. This should perform some Python slicing. So no db involved after the first evaluation of your queryset. – Nicolas W. Oct 24 '16 at 12:13