Empty Thoughts

Retrieving Selective Fields with Django

Today there was a question on the #django IRC channel from a user name “Joe” about how to go about retrieving selective fields from a table. Joe had a model structure that looked something like:

class Category(models.Model):
    name = models.CharField(max_length=50)
    description = models.CharField(max_length=200)

class Link(models.Model):
    title = models.CharField(max_length=100)
    slug = models.SlugField(prepopulate_from=("title",))
    url = models.URLField()
    category = models.ForeignKey(Category, null=True, blank=True)
    big_ass_notes = models.TextField(blank=True)
    updated = models.DateTimeField(editable=False)
    user = models.ForeignKey(User)

The issue is that Joe wants to be able to get all Links that are tied to a specific Category but only retrieve select fields. You might want to do something like this if you needed to retrieve a subset of information and you wanted to keep from pulling particularly large fields like BLOBs or Text fields.

At first I had suggested that Joe use the extra() method to retrieve just the fields that he wanted from the Link model without having to pull in all the fields from the model. We tried something like this:

>>> from delinkuent.models import Category, Link
>>> Category.objects.get(pk=1).extra(select={'url': 'delinkuent_link.url'})
Traceback (most recent call last):
  File "<console>", line 1, in <module>
AttributeError: 'Category' object has no attribute 'extra'

Well obviously that does not work. The problem is that extra() is a method on the QuerySet. With get() we are only getting a single model instance object. So we tried the following:

>>> from delinkuent.models import Category, Link
>>> Category.objects.get(pk=1).link_set.extra(select={'url': 'delinkuent_link.url'})
[<Link: Django Web Framework>, <Link: The B List>]

Well that looks better, but when we check The SQL That Django is Generating we discover the following:

>>> from django.db import connection
>>> connection.queries[-1]['sql']
u'SELECT "delinkuent_link"."id","delinkuent_link"."title","delinkuent_link"."slug","delinkuent_link"."url","delinkuent_link"."category_id","delinkuent_link"."big_ass_notes","delinkuent_link"."updated","delinkuent_link"."user_id",(delinkuent_link.url) AS "url" FROM "delinkuent_link" WHERE ("delinkuent_link"."category_id" = 1) ORDER BY "delinkuent_link"."updated" DESC'

Unfortunately that didn’t work. Notice that all we did was get an extra url item in addition to the url item that was part of the regular SELECT clause. Hence the name extra(). Unfortunately there’s no only().

After working on it a bit, and reviewing my post on Finding Lookup Items That Are Not Used, I realized that I could reverse the logic and approach the problem from a different angle.

Instead of trying to get all Links from the Category, we could get all Links where the Category is the one we want. At first you might think that getting all Links will present a problem since the field we’re trying to ignore is in the Link model. You’re right, except that Django has this cool QuerySet method called values(). The values() method will return a ValuesQuerySet, essentially a QuerySet that evaluates to a list of dictionaries. The values() method accepts a list of field names that you want to include in the ValuesQuerySet dictionary.

Now that we know about values() and reversing the logic, Joe can get what he needs by doing the following:

>>> from delinkuent.models import Category, Link
>>> Link.objects.filter(category__pk=1).values('url')
[{'url': u'http://www.djangoproject.com/'}, {'url': u'http://www.b-list.org/'}]

I always like to take a look at the SQL that Django is generating to make sure I’m getting what I want:

>>> from django.db import connection
>>> connection.queries[-1]['sql']
u'SELECT "delinkuent_link"."url" FROM "delinkuent_link" WHERE ("delinkuent_link"."category_id" = 1) ORDER BY "delinkuent_link"."updated" DESC'

Notice that the big_ass_notes field, the one we are trying to avoid, is not being selected. Perfect!!!

But There’s More…

After working through this post it occurred to me that we didn’t really need to reverse the logic. This is where looking at the SQL that is being generated is so helpful. I decided to try it using the FOO_set approach we tried earlier, but with a twist:

>>> from delinkuent.models import Category, Link
>>> Category.objects.get(pk=1).link_set.values('url')
[{'url': u'http://www.djangoproject.com/'}, {'url': u'http://www.b-list.org/'}]

If you notice that when we use FOO_set, if we add on the values() method before it gets evaluated, then we will only get the fields specified in the values method. Here’s the resulting SQL:

>>> from django.db import connection
>>> connection.queries[-1]['sql']
u'SELECT "delinkuent_link"."url" FROM "delinkuent_link" WHERE ("delinkuent_link"."category_id" = 1) ORDER BY "delinkuent_link"."updated" DESC'

Still perfect!!!

Sometimes it takes a bit of work to get where you need to be with the Django ORM. Although, in the end you find that there is a ton of flexibility built into it.

---

Discussion

---