How to filter by table-A from table-B (table-A is linked to table-B, not the otherwise)

Question

I have the following models:

# models.py

class Movement(models.Model):
    class Direction(models.TextChoices):
        SENT = 'S', _('Sent')
        RETURNED = 'R', _('Returned')

    case = models.ForeignKey(Case, on_delete=models.CASCADE, unique=False, null=False, blank=False, db_index=True, db_column='case_id')
    direction = models.CharField(unique=False, null=False, blank=False, db_index=False, max_length=1, choices=Direction.choices, default=Direction.SENT, db_column='direction')
    date = models.DateField(auto_now=False, auto_now_add=False, db_index=True, default=current_date, db_column='date')


class Case(models.Model):
    class Meta:
        constraints = [models.UniqueConstraint(fields=['number', 'year', 'kind', 'police_station'], name='unique_case2')]

    class Kind(models.TextChoices):
        MISDEMEANOR = 'M', _('Misdemeanor')
        TRAFFIC = 'T', _('Traffic')

    police_station = models.ForeignKey(PoliceStation, on_delete=models.PROTECT, unique=False, null=False, blank=False, db_index=True, db_column='police_station_id')
    name = models.CharField(unique=False, null=False, blank=False, db_index=True, default='', max_length=100, db_column='name')
    number = models.SmallIntegerField(unique=False, null=False, blank=False, db_index=True, db_column='number')
    year = models.SmallIntegerField(unique=False, null=False, blank=False, db_index=True, default=current_year, db_column='year')
    kind = models.CharField(unique=False, null=False, blank=False, db_index=False, max_length=1, choices=Kind.choices, default=Kind.MISDEMEANOR, db_column='kind')
    registered_at = models.DateTimeField(auto_now=False, auto_now_add=False, db_index=True, default=current_datetime, db_column='registered_at')

Some facts about the Movement table:

• The Movement table may have multiple records for the same case.
• The direction field can not have the same value for two records in a row. (ex: if the last Movement record for case_id = 2 is the direction = 'S', the next movement must be direction = 'R').

And I would like to filter cases by their last movement's directions from CaseAdmin class, into three types:

1. Sent.
2. Returned.
3. Has no movement yet.

Now how to build a query to filter cases by their last movement?

Filter rules:

• Only the last movement record should be considered.
• Last movement record should be determined by its -date not id.
• The 'Sent' option, must show the available cases in which their last Movement direction = 'S'.
• The 'Returned' option, must show the available cases in which their last Movement direction = 'R'.
• 'Has no movement yet' option, must show the available cases in which they have no Movement records yet.

I thought about it, and I came up with two solutions:

1. Add an extra boolean field called is_latest to the Movement table, in order to know which record is to be considered as the latest movement when building the query. Which needs two update statements for the Movement table whenever each new movement record is added for the same case.
2. Or, add an extra foreign key field called last_movement to the Case table that points to the latest movement record in the Movement table. I think this one is better since it needs only one update statement for the Case table whenever each new movement record is added for the same case.

Both solutions need to be maintained when each new movement record is got inserted into the Movement table.

But I was wondering if there is another good / better / practical solution for this.

Thanks!

Answer 1

There is no need to define extra ForeignKeys or add extra fields to the Movement table. We can work with a Subquery expression ^[Django-doc]:

from django.db.models import OuterRef, Subquery


class CaseAdmin(admin.ModelAdmin):
    def get_queryset(self, *args, **kwargs):
        return (
            super()
            .get_queryset(*args, **kwargs)
            .annotate(
                last_direction=Subquery(
                    Movement.objects.filter(case_id=OuterRef('pk'))
                    .order_by('-date')
                    .values('direction')[:1]
                )
            )
        )

    list_filter = [LastDirectionFilter]

with as LastDirectionFilter:

from django.contrib import admin


class LastDirectionFilter(admin.SimpleListFilter):
    title = 'Last direction'
    parameter_name = 'last_direction'

    def lookups(self, request, model_admin):
        return [
            ('S', 'Sent'),
            ('R', 'Returned'),
            ('-', 'No movement yet'),
        ]

    def queryset(self, request, queryset):
        value = self.value()
        if value == '-':
            return queryset.filter(last_direction=None)
        elif value is not None:
            return queryset.filter(last_direction=value)
        return queryset

here we thus filter on our annotation that can be None if there is no movement yet, or 'S' or 'R'.