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.

December 16, 2009  Tags: ,   Posted in: All ENGLISH articles, Technical

Leave a Reply