Django Admin: show single instance of duplicate instances

Question

I'm trying to show a single instance (db row) from a model where several instances share the same field (column) value for several rows. To clarify that statement,I have the following situation:

ID/Title/Slug/Modified
1   Car     A  1s ago
2   Car     A  2s ago
3   House   B  1s ago

If the above small table were my db I want my Django admin page to show distinct rows based on my slug field (column) show the last edited version (I have another column for time... So the above table would show up in the admin page as follows:

ID/Title/Slug/Modified
1   Car     A  1s ago
3   House   B  1s ago

Although row 1 & 2 have different pk's they have the same slug, I want only one of them with the later time...

I can achieve this in my views.py as follows

existing_data = MyModel.objects.filter(slug=slug).latest('modified')

but that's because I'm looking for a specific instance of a slug.. If I wasn't I could also use group_by...

I'm trying to get this display in admin page. I've tried the following techniques in a model manager,

class ModelManager(models.Manager):
    def get_query_set(self):
        return super(ModelManager, self).get_query_set().group_by('title')

but I get this error

'QuerySet' object has no attribute 'group_by'

Then I was reading this part of the Django book and they implemented raw sql in a model manager which I tried copying to my situation as follows,

class ModelManager(models.Manager):
    def uniques(self):
        cursor = connection.cursor()
        cursor.execute("""
            SELECT *
            FROM eventform_event
            GROUP BY slug
            """)
        return [row[0] for row in cursor.fetchone()]

Im my model I have

objects = ModelManager()

I'm just not sure how to get the admin model to view my custom manager that doesn't override the get_query_set. When I did use this custom manager to override the get_query_set I get this error

'long' object has no attribute '__getitem__'

I've also tried testing 'values(), values_list(), distinct() etc but they all give me errors... distinct() tells me my database (mysql) doesn't not support this feature.. now sure if I have to switch databases to achieve this feature and now I'm out of ideas to experiment with... Anybody know how to achieve this functionality.. Thanks.

#

In my admin.py page I can get side filter (list_filter) to show unique entries based on the slug column per this thread recommendation...

Unfortunately I can't get the rows displayed in the admin page for my model to be unique based on a certain column...

Answer 1

If you're using Django 1.4+ with PostgreSQL you can use queryset.distinct() with a *fields argument. If you're using an older version of Django or a different database engine let me know and I'll work out a different way to do it.

So if your model looks something like this:

from django.db import models

class TestModel(models.Model):
    title = models.TextField(max_length=150)
    slug = models.TextField(max_length=150)
    modified = models.DateTimeField(auto_now=True)

Then you can do this in the ModelAdmin's queryset method:

from django.contrib import admin

import models

class TestModelAdmin(admin.ModelAdmin):
    list_display = ('title', 'slug', 'modified')

    def queryset(self, request):
        qs = super(TestModelAdmin, self).queryset(request)
        qs = qs.order_by('slug', '-modified').distinct('slug')
        return qs

admin.site.register(models.TestModel, TestModelAdmin)

Hopefully that helps.

Edit: Okay, if you're using MySQL it's not quite so neat. You can't use raw SQL because the Django admin expects to work with a QuerySet and not a RawQuerySet. Even if we could use raw SQL it wouldn't be quite as simple as GROUP BY slug because if you do that MySQL gives you results in the order they appear in the table. So if your data looks like this:

+----+-------+------+---------------------+
| id | title | slug | modified            |
+----+-------+------+---------------------+
|  1 | Car   | A    | 2013-06-30 20:18:06 |
|  2 | House | B    | 2013-06-30 20:18:12 |
|  3 | Car   | A    | 2013-06-30 21:02:51 |
|  4 | Thing | C    | 2013-06-30 22:08:00 |
|  5 | House | B    | 2013-06-30 22:08:05 |
+----+-------+------+---------------------+

Grouping by slug gives you ids 1,2, and 4 regardless of any order by clause. We actually want ids 3, 4, and 5 because otherwise the Django admin won't be showing us the most recently modified entry (which is what you say you want). So before we can do any grouping we have to select from a pre-sorted table.

The best way I can think of is to change the queryset method in the answer I gave for PostgreSQL to:

def queryset(self, request):
    qs = super(TestModelAdmin, self).queryset(request)
    qs = qs.extra(where=[
        "id IN (
            SELECT id FROM (
                SELECT * FROM myapp_testmodel ORDER BY modified DESC
            ) AS last_modified
            GROUP BY slug)"
    ])
    return qs

You'll have to change id to the name of primary key of your table, and myapp_testmodel to the name of your table.