Django trap with LIMIT queries
I started doing Django after the magic-removal, but there is some magic left (but mostly good magic) in it and it’s a good thing to be aware of it! Always dig deeper, that’s at least my motto.
The model used here is the following:
class Item(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(maxlength=100)
def __str__(self):
return self.name
The LIMIT basics
Be sure to really understand what happens when you are using limit query, which is the array slicing syntax. Calling the following:
Item.objects.all()[2:4]
will execute this query
SELECT `core_item`.`id`,`core_item`.`name` FROM `core_item` LIMIT 2,2
BUT not until you are using the data. Using the shell is therefore sometimes a bit misleading, since calling the above results in printing the result, so it is being used at this moment, which inside a script would not be the case.
>>> Item.objects.all()[2:4]
[
Here you can see that two items are being printed, right after the call, but that’s a shell thing, remember that!
Mixing slicing and LIMIT
If you mix the usage of the slicing operator and the limit query on a queryset you better know what you are doing. Let’s see first:
>>> items = Item.objects.all()[2:4] # Django basics, this is LIMIT.
>>> items[5] # Looks like it should be slicing, is LIMIT!
Ooops. How come? Why is there a fifth element at all? Let’s investigate a bit more! Is items not what we expect? How many elements does items contain? What is being returned, when calling the first line of the above?
>>> len(items)
2
>>> type(items)
Uhu, ok. The Django documentation does also explain that very well, so be aware, you don’t get a list returned, but a queryset!
So what is happening here in detail? This line >>> items = Item.objects.all()[2:4] clones a queryset into the variable items. So items is not a list, as the syntax might would let you expect, but a queryset! This assignment is actually only a method call. The method call to __getitem__() in django.db.model.query.QuerySet, this does finally return a cloned queryset with adjusted LIMIT parameters (effectively this is called self._clone(_offset=offset, _limit=limit)). So there is no query execution at this point.
And if the next call is now items[5] then the same method is being called again with LIMIT 5. So there is no slicing done here, as one would expect. This second call is also just a simple method call to __getitem__() and since the shell prints the result right away the query is actually being fired!
The work around
In order to make Django do what you expect you need to be more explicit than normally:
>>> items = list(Item.objects.all()[2:4])
>>> items[0]
>>> type(items)
This makes Django fire the query right away and return a real list, as you can see above. Now you can use the result as one would expect when reading it as normal Python code (without knowing that it is Django).
This has two effects: you are executing the query earlier and you have no QuerySet object anymore that you can work with. But that might be intended in this case.
Summary
>>> # next line fires: SELECT * FROM `core_item` LIMIT 2,2
>>> Item.objects.all()[2:4]
[
>>> items = Item.objects.all()[2:4] # fires no query!
>>> # next line fires: SELECT * FROM `core_item` LIMIT 1
>>> items[0]
But
>>> items = list(Item.objects.all()[2:4]) # fires: SELECT * FROM `core_item` LIMIT 2,2
>>> items[0]
As I have mentioned above, you better know your tools! Django is very nicely using the overriding facilities that Python offers, but as usual, when you only scratch the surface you might be surprised and think “What is happening here” but don’t worry, the engine below is well thought through and really powerful! Let’s keep Djangoing …
Update
Thanks for Martina’s comment, which of course makes it much better! I modified the article accordingly.
