Django: using values() and get_FOO_display()?

Question

I'm trying to improve some existing code which originally took 3 minutes to prepare a large dataTable (then returned by Ajax). The old code iterated over a large querySet, gathering information from a variety of related objects. From what I've read, and from monitoring the SQL log, iterating over querysets is generally a bad idea, because SQL is executed for each item. Instead, I've been using values to gather information in a single SQL statement, then iterating through that. Using this technique, I've reduced the execution time to under 15 seconds (and I'm still not done). However because I'm no longer using model objects, I can't use get_FOO_display(). Is there a way to use this functionality while using values()?

Simplified, the original was :

for user in users:
   data.append(user.get_name_display())  # Appends 'Joe Smith'
return data

And the new code is:

for user in users.values('name'):
   data.append(user['name'])  # Appends 'JSmith001', which is incorrect
return data

Also, if there's some other way to preserve the creation of model objects yet only requires a single SQL statement on the backend, I'd love to know about it. Thanks!

Answer 1

In general, it'll probably be better and easier to use a Manager-based query that returns model objects. It sounds like the issue with your original approach is not that you were iterating over your queryset (as @ahmoo says, this isn't a performance issue), but that within your iteration loop you were getting additional related objects, requiring one or more additional queries for each record.

There are several ways to improve performance with queries that still return model instances:

• It sounds like the most relevant is select_related(), which will effectively do a table join on the initial query to include data for all objects related by foreign keys.

• If that's insufficient, you can also add data to your model instances with extra(), which allows you to stick subqueries into your SQL.

• And if all that fails, you can perform raw SQL queries using the .raw() method on a Manager instance, which will still return model instances.

Basically, if you can do it in SQL in a way that gives you one row per instance, there's a way to do it in Django and get model instances back.

To answer your original question, though, you can get the display name through the Field class - it's just ugly:

def get_field_display(klass, field, value):
    f = klass._meta.get_field(field)
    return dict(f.flatchoices).get(value, value)

# usage
get_field_display(User, 'name', 'JSmith001')

Answer 2

iterating over querysets is generally a bad idea, because SQL is executed for each item

That's not true. Below is taken from the official docs:

A QuerySet is iterable, and it executes its database query the first time you iterate over it

I think the problem has to do with the definition of the users from your code. What did you assign to it?