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

Using Django The Admin
1

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!

2

If you have an instance of the model Case named case, then the most recent movement for that case would be:
most_recent_movement = case.movement_set.order_by('-date').first()

Regarding finding the cases with a specific last Movement, the easiest I can think of is this:

Case.objects.annotate(
  last_direction = Subquery(
    Movement.objects.filter(
        case=OuterRef('id')
    ).order_by('-date')[:1].values('direction')
  )
).filter(last_direction='S')  # or 'R' as necessary

The “has no movement yet” is easier:
Case.objects.filter(movement=None)