Django admin custom field fetching result of latest related object by condition

Question

I have three models

class Customer(models.Model):
    created = models.DateTimeField(auto_now_add=True)
    # some other fields, which don't matter

class ActivityStatus(models.Model):
    created = models.DateTimeField(auto_now_add=True)
    name = models.CharField(max_length=5)

class Activities(models.Model):
    created = models.DateTimeField(auto_now_add=True)
    status = models.ForeignKey(ActivityStatus)
    planned_execution_date = models.DateField()

also I have two them in admin

class CustomerAdmin(admin.ModelAdmin):
    fields = () # doesn't matter really
    list_display() # doesn't matter really

class ActivityAdmin(admin.ModelAdmin):
    fields = () # doesn't matter really
    list_display() # doesn't matter really

I want to add custom field (last_activity_planned_date) to be displayed and sorted on changelist_view of CustomerAdmin. Here is the condition: - show planned execution date of latest activity of Customer but only if this activity has status_id = 2. Otherwise show nothing

Example 1

Customer 1 has 2 activities - the latest has status_id = 2 and planned_execution_date = 2017-09-17

changelist_view:

|id|last_activity_planned_date|

|1|2017-09-17|

Example 2

Customer 1 has 2 activities - the latest has status_id = 3 and planned_execution_date = 2017-09-27

|id|last_activity_planned_date|

|1|null|

Antoine Pinsard · Answer 1 · 2018-10-04T07:22:48.153

You can add custom fields in list_display:

class CustomerAdmin(admin.ModelAdmin):
    fields = () 
    list_display = ('last_activity_planned_date',)

    def last_activity_planned_date(self, obj):
        latest_activity = obj.activities.order_by('-planned_execution_date').first()
        if latest_activity and latest_activity.status_id == 2:
            return latest_activity.planned_execution_date
        else:
            return None

However, this would be quite complicated to make this column orderable. Even in raw SQL, it seems like a complex query. In Django 2.1 support for query expressions in admin_order_field was added, but still I can't figure out a decent SQL query to achieve such sorting.

A solution is to replicate this information in a field Customer.last_activity_planned_date (with db_index=True to improve ordering performance).

If you can afford this information to be updated only periodically, you can setup a cronjob to update it every 15mn, 1h or 1 day (according to your needs and the resources it takes to make the update).

If you need this information to be always exact with no delay, you will have to update it every time an Activities entry is created, updated or deleted. There are various ways to do this depending on your needs, including SQL triggers and Django signals. This works quite well, but is not hundred percent sure, so even if you use this solution, I would advise to setup a daily cronjob to make sure all data is consistent.

Sure, I know about custom fields and that way it is not complicated. My main goal is to make it orderable which seems to be impossible( — GriMel, Oct 04 '18 at 06:59
I think you must first figure out the SQL query for the ORDER BY clause, and then try to translate it into Django Query Expression. However, even if you manage to find the SQL query, it might have a very expensive cost regarding performance. I usually don't like redundancy. But in such case, I think it would be relevant to replicate this information in a new field `Customer.last_activity_planned_date` (likely with `db_index=True` for performances). You can either update this field when an `Activities` entry is created, modified or deleted, or update it regularily with a cron job. Or even both. — Antoine Pinsard, Oct 04 '18 at 07:12

GriMel · Accepted Answer · 2018-10-07T11:36:06.263

Using this anwer I managed to find the solution

from django.db.models.expressions import Subquery, OuterRef
ACTIVITY_STATUS_NEW = 2

def get_queryset(self, request):
    queryset = super().get_queryset(request)
    activities_per_customer = Activities.objects.filter(customer_id=OuterRef('pk'))
    latest_activity_per_customer = (activities_per_customer.order_by('pk', '-created')).distinct('pk')
    latest_active_activity_per_customer =(latest_activity_per_customer.filter(status_id=ACTIVITY_STATUS_NEW))
    queryset = queryset.annotate(_last_activity_planned_date=Subquery(latest_active_activity_per_customer.values('planned_execution_date')[:1]),)

    return queryset

and then

list_display = ('id', 'last_activity_planned_date')

def last_activity_planned_date(self, obj):
    return obj._last_activity_planned_date

last_activity_planned_date.short_description = "Optional description"

Django admin custom field fetching result of latest related object by condition

2 Answers2