Django : random ordering(order_by('?')) makes additional query

Question

Here is sample codes in django.

[Case 1]

views.py

from sampleapp.models import SampleModel
from django.core.cache import cache

def get_filtered_data():

    result = cache.get("result")

    # make cache if result not exists
    if not result:
         result = SampleModel.objects.filter(field_A="foo")
         cache.set("result", result)

    return render_to_response('template.html', locals(), context_instance=RequestContext(request))

template.html

  {% for case in result %}
  <p>{{ case.field_A}}</p>
  {% endfor %}

In this case, there's no generated query after cache made. I checked it by django_debug_toolbar.

---

[Case 2]

views.py - added one line result = result.order_by('?')

from sampleapp.models import SampleModel
from django.core.cache import cache

def get_filtered_data():

    result = cache.get("result")

    # make cache if result not exists
    if not result:
         result = SampleModel.objects.filter(field_A="foo")
         cache.set("result", result)

    result = result.order_by('?')

    return render_to_response('template.html', locals(), context_instance=RequestContext(request))

template.html - same as previous one

In this case, it generated new query even though I cached filtered query.

---

How can I adapt random ordering without additional queryset?

• I can't put order_by('?') when making a cache. (e.g. result = SampleModel.objects.filter(field_A="foo").order_by('?')) Because it even caches random order.

• Is it related with 'django queryset is lazy' ?

Thanks in advance.

Answer 1

.order_by performs sorting at database level.

Here is an example. We store lasy queryset in var results. No query has been made yet:

results = SampleModel.objects.filter(field_A="foo")

Touch the results, for example, by iterating it:

for r in results:  # here query was send to database
    # ...

Now, if we'll do it again, no attempt to database will be made, as we already have this exact query:

for r in results:  # no query send to database
    # ...

But, when you apply .order_by, the query will be different. So, django has to send new request to database:

for r in results.order_by('?'):  # new query was send to database
    # ...

Solution

When you do the query in django, and you know, that you will get all elements from that query (i.e., no OFFSET and LIMIT), then you can process those elements in python, after you get them from database.

results = list(SampleModel.objects.filter(field_A="foo"))  # convert here queryset to list

At that line query was made and you have all elements in results.

If you need to get random order, do it in python now:

from random import shuffle
shuffle(results)

After that, results will have random order without additional query being send to database.