Django. How I can optimize db query?

Question

I have two models:

class Status(models.Model): 

     CHOISES = ( 
         ('new', 'New'), 
         ('in_progress', 'InProgress'), 
         ('on_review', 'OnReview'), 
         ('tested', 'Tested'), 
         ('delivered', 'Delivered') 
     ) 

    status_type = models.CharField( 
    max_length=11, 
    choices=CHOISES, 
    primary_key=True) 

class Task(models.Model):
    name = models.CharField(max_length=200, blank=False)
    status = models.ForeignKey(status)

part of my view:

def task_list(request):

    all_tasks = Task.objects.all()
    tasks = {}

    tasks['new'] = all_tasks.filter(status_id='new')
    tasks['in_progress'] = all_tasks.filter(status_id='in_progress')
    tasks['on_review'] = all_tasks.filter(status_id='on_review')
    tasks['tested'] = all_tasks.filter(status_id='tested')
    tasks['delivered'] = all_tasks.filter(status_id='delivered')
....

part of my template:

{% for item in new %}
    <div id="{{ item.pk }}">
        {{ item.name }}
    </div>
{% endfor %}
</div>
{% for item in in_progress %}
    <div id="{{ item.pk }}">
        {{ item.name }}
    </div>
{% endfor %}  
.....

The question is, is there any way to optimize all my querysets, or it is imposible to select five filters for one db call?
Like I understand, if I save in view only this call all_tasks = Task.objects.all() and then put logic in template, like this:

{% for item in all_task %}
{% if item.status_id == "new" %}
....

it will be exaclty five calls too
Hope my question is not too broad

Alex Morozov · Accepted Answer · 2016-01-26 12:37:18Z

4

Just group your tasks by status in your view:

from itertools import groupby

def task_list(request):
    all_tasks = Task.objects.select_related('status').order_by('status')
    tasks = {status: list(tasks)
             for status, tasks
             in groupby(all_tasks, lambda x: x.status)}

And use them in your template like:

{% for task in tasks.new %}
  {{ task }}
{% endfor %}

edited Jan 26, 2016 at 12:37

answered Jan 26, 2016 at 9:56

Alex Morozov

6,01327 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ivan Semochkin Over a year ago

great! thank you. Maybe it is stupid question, but how I can get task objects in template now? I dont need ordering list, with objects one by one. I need to put objects in five columns for each status, like in trello.com, so I need some condition. If I use {% if %} tag for check status name, it will be db call anyway?

vsd Over a year ago

Queryset should be Task.objects.select_related('status').order_by('status'). In this case there will be no extra db queries.

Wtower · Accepted Answer · 2016-01-26 10:03:33Z

2

A possibility is to change your CHOICES (typo in there) to map to something that makes sense in ordering, eg integers.

Then perform a single query with orderby and in the template use the ifchanged tag.

For whatever you may try, be careful not to fall in the trap of implementing SQL logic in Django just to avoid an extra query.

In any case, I would recommend you to use something like django-debug-toolbar to have a grasp on times for any alternative.

answered Jan 26, 2016 at 10:03

Wtower

20.1k12 gold badges110 silver badges86 bronze badges

Collectives™ on Stack Overflow

Django. How I can optimize db query?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related