Setting the initial value for Django's ModelChoiceField

Recently, I worked with a Django form that utilized the ModelChoiceField. That is a convenient field that normally is rendered as an HTML select tag, which usually appears as a drop-down menu on a web-page.

A ModelChoiceField is specified as:

    class MyForm(forms.Form):
        my_field = forms.ModelChoiceField(queryset = MyModel.objects.all())

As you can see, this field is used to easily specify a drop-down for all items in a table (or whatever the queryset specifies). The model is represented as the output of its __unicode__() function. If you evaluate the form after it was posted, the value for the field is going to be an instance for the actual model whose unicode representation was selected.

The problems for me started when I tried to set an initial value for the field: In an 'edit' form, I wanted all fields to reflect whatever had been saved for a particular model instance, of course. As I said above, if you evaluate the form after it has been submitted you get an actual model instance. Naturally, you would think that initial values would be set in a symmetric manner, by specifying a model instance:

    form = MyForm(initial = { 'my_field' : some_model_instance })

Sadly, this doesn't work. And try as I might, I couldn't find an answer to this on the Internet either (more on that in a moment). So, after looking at the Django code, it finally dawned on me that you need to specify the ID of the model instance as the initial value:

    form = MyForm(initial = { 'my_field' : some_model_instance.id })

That works now. It's a bit unfortunate that the retrieval value (a model instance) and the initial value (the ID of a model instance) are of a different type. It's an inconsistency in the Django API, I think. But in the end, I probably should have at least tried that one a bit sooner.

The surprising thing is that I couldn't find any discussion of this anywhere on the Internet. I should mention here that I am using Yahoo as my default search engine. Shortly after I finally found the solution, it occured to me to try Google. And wouldn't you know it? Right there, third hit from the top, I had the answer.

Why did Yahoo not give me this result? Well, the answer was discussed on Google Groups. Is Yahoo not indexing those? Or is Google not letting them index it?

Either way, that small issue between Yahoo and Google cost me a few hours of frustration. So, I'm posting the solution here on a non-Google page, so that Yahoo users may also find the answer to that problem in the future.


You should follow me on twitter here.

How to do "count" with "group by" in Django

Faced with the 'age old' problem of having to do a group by query in Django, I finally came across a solution.In short, you use the lower-level query interface directly:

    q = MyModel.objects.all()
    q.query.group_by = ['field_name']

When you then iterate over 'q', you get the results grouped by the value of whatever was in 'field_name'. Great! So far, so good.

Well, a word of caution at this point: Using the low-level query API is not really something Django wants you to do, apparently. In fact, try this with sqlite and it works. Try it with PostgreSQL and it does not (all sorts of error messages). So, your mileage may vary, depending on which database you are using. Ok, let's assume that your database is fine with this...

The only challenge for me now was that I had to do a count for this.
If you form your query using the usual methods of Django's ORM, you will be disappointed. For example, this here will not work:

    q = MyModel.objects.all()
    q.query.group_by = ['field_name']
    q.count()

It appears as if this returns only the count of the first group, not a list of counts, as you would expect.

The solution is to wade a bit deeper into the low-level query API. We can instruct the query to add a count-column. In fact, this results in merely a single column being returned, just like COUNT(*). It goes like this:

    q = MyModel.objects.all()
    q.query.group_by = ['field_name']
    q.query.add_count_column()

Since this returns counts, rather than complete objects, we now need to get the individual group counts as a list of values. We add one more line:

    q.values_list('id')

The values_list() function gives you not instantiated objects but instead the tuples of values for each object. The tuples contain only the fields you specify by name in the call to values_list(). Except, we have manually added the count column with the add_count_column() function. That count column is always returned first. So, what you get as result is something like this:

    [ (3,1), (19,8), ... ]

The first value of each tuple is the count, the second value is the value of the 'id' field of the last occurrence of the grouped model in the table. If you specify something other than 'id', you get the same thing: First the count and then the value of that other field.

But that's not what we want, right? We want a list of counts. We could manually extract the first element in each tuple, but Django offers us a shortcut:

    q.values_list('id', flat=True)

Setting flat=True tells the values_list() function to just return the first element of each tuple in a plain list (not a list of tuples). And since the first element now is the count column, we finally get what we want: A list of the counts for each group.

Note that we could have specified any other fields in the call to values_list(), not just 'id'. Because we specified flat those fields are ignored. It seems, though, that at least one field needs to be specified here, even though it won't be considered as part of the output.


You should follow me on twitter here.

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.