Django: get distinct field selection

I recently have been dinging around in Django‘s ORM because I needed a certain ‘fields … group by’ selection. I was very impressed by the logic that the Django developers put in there. Especially the joins and aggregations that have been released with version 1.1. However, after a hours of testing and googling, I was not able to solve my problem with it. I needed a distinct selection like this:

SELECT id, name FROM myapp_model WHERE owner_id='1' GROUP BY name;

This would have been (easily) possible except for the WHERE clause on ownership that screwed me. Since the SQL statement was that simple I decided to write a bypass function that get’s the data straight from the database. It’s readonly access and I dont do anything more with it then create a list of links. Even more so, from a KISS point of view this code is easier to read back after several months.

And for reusability I turned it in to a function. It takes the model and the distinct field, and for my purpose the needed ownership as parameter:

from django.db import connection

def get_latest_objects(model_name=None, distinct_field=None, user_id=None):
    """ Get lastest distinct selection (as tuples) of a given model
    """
    model_name = model_name.lower()
    query = ("select id,%(distinct_field)s from myapp_%(model_name)s "
                "where owner_id='%(user_id)s' "
                "group by %(distinct_field)s;") % {
                    'model_name': model_name,
                    'distinct_field': distinct_field,
                    'user_id': user_id,
                    }
    cursor = connection.cursor()
    cursor.execute(query)

There’s not much to it, I just hope it helps you to avoid ‘the hard way’. And it helps to keeps your view methods more readable because you dont need anything more then this:

latest_objects = get_latest_objects(model_name='MyModel',
                        distinct_field='name',
                        user_id=request.user.id)

What is returned (latest_objects) is a list of tuples that you can unpack in your templates straight away. Note that there’s no error checking in there, because I know what I’m doing Besides, worst case you get back an empty list and possible errors are caught when you write your tests. You do write those don’t you?

Grtz Gerard.