Retrieving full objects with custom SQL in Django

While working with Django, I recently had to retrieve some model objects via custom SQL. Looking for examples on how to do this, I noticed that most tutorials describe how to retrieve specific values via SQL, but not entire model objects. The solution is actually very simple. But since I couldn't find it described anywhere, I thought I share it here.

The usual approach goes something like this:

   from django.db import connection, models
   class CustomManager(models.Manager):
       ...
       query = "SELECT id FROM mymodel WHERE ..."
       cursor = connection.cursor()
       cursor.execute(query)
       return [i[0] for i in cursor.fetchall()]

This then returns the IDs of the objects that were selected. If you want to return actual objects, you can modify the last line into this:

    return self.filter(id in [i[0] for i in cursor.fetchall()])

However, the problem with this approach is that we are executing two queries now: The custom SQL query, followed by the self.filter() query.

Here then is a very simple way to get complete objects, with just a single, custom SQL query:

   class CustomManager(models.Manager):
       ...
       query = "SELECT * FROM mymodel WHERE ..."
       cursor = connection.cursor()
       cursor.execute(query)
       return [MyModel(*i) for i in cursor.fetchall()]

Rather than making a list of IDs, which is then used to query for the actual objects, we make a list of the complete objects. We can do that, because we changed the custom SQL: Instead of selecting just the ID column, we are now selecting all columns (SELECT * ...).

With custom SQL queries we get a list for each row. Fortunately, the order of columns in each row reflect the order of arguments to the model's __init__() function. As a result, we can use each row's list representation as positional arguments for __init__().

You should follow me on twitter here.