Can a Django query filter with a relationship reference a parent's field value?

I have a Training and TrainingAssignment table in my django app. Users sign into the app and complete a Training which creates a TrainingAssignment record. The Training table has a field, days_valid which is used to determine whether or not a user has to re-take a training.

I want an admin to be able to change days_valid on the fly to "invalidate" previously completed trainings. (e.g. changing a training thats good for a year to only good for one month.). I don't want the admin's changing of days_valid to go out and change the expiration date of thr TrainingAssignment record...I want those records to remain untouched for audit purposes. Instead, my code creates a new assignment if the employee is due.

models.py

class Training(models.Model):
    title = models.CharField(max_length=200)
    days_valid = models.IntegerField()


class TrainingAssignment(models.Model):
    training = models.ForeignKey(
        Training,
        on_delete=models.CASCADE,
        related_name='training_assignments',
        blank=True,
        null=True
    )
    date_started = models.DateTimeField(null=True,blank=True) 
    date_completed = models.DateTimeField(null=True, blank=True)
    date_expires = models.DateTimeField(null=True, blank=True)

In a query, I need to be able to get all Trainings where TrainingAssignment's date_completed + the Training's days_valid <= today. (ie. user completes a TrainingAssignment on 6/1/23 and it's Training record is valid for 45 days, so when they log in on 6/15/23 the queryset should be empty but on 7/19/23 the queryset should contain that training.)

now = timezone.now().date()    
trainings = Training.objects.filter(
    training_assignments__date_completed__lte=now+datetime.timedelta(days=training_assigments__training.days_valid))

But this obviously errors out with training_assignments__training.days_valid is undefined

Can a query reference a field of itself? I am not even sure of the right question to ask. Part of me feels I am overcomplicating this. Another part of me feels the Django-ORM already solved this problem and I just haven't seen it yet.

Solution

If you're using Postgres as your database and really want this as a single query, you can use an F() Expression to get the days_valid from the Training instance related to the TrainingAssignment. The filter subtracts the days_valid from today's date and compares that with the date_completed of each TrainingAssignment to find any that have expired. After we run the filter, we use values_list to tell Django to only get the field that we're interested in (Training), and then we use distinct to remove redundancies.

Note that the F() Expression can't be used directly inside timedelta, so we create a 1-day timedelta and multiply by the F() Expression (credit to Lutz Prechelt)

from django.db.models import F

now = timezone.now().date()    
trainings = TrainingAssignment.objects.filter(date_completed__lte=now - datetime.timedelta(days=1) * F("training__days_valid")).values_list("training__title", flat=True).distinct()

This won't work for SQLite because of the F() Expression multiplication with datetime.timedelta. You can use the loop below to accomplish the same without the F() Expression.

needed_trainings = []
now = timezone.now().date() 
for training in Training.objects.all():
     if TrainingAssignment.objects.filter(training=training, date_completed__lte=now - datetime.timedelta(days=training.days_valid)).exists():
         needed_trainings.append(training.title)