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

by

1👍

✅

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"
👤GriMel

1👍

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.

👤Antoine Pinsard

Leave a comment