Django query with dynamic timedelta

Question

Hello, I am facing an unexpected difficulty to resolve this query. I appreciate your help.

I have two models:

class Rating(models.Model):
    revision_period = models.PositiveSmallIntegerField(
        verbose_name=_('Period to revision'),
        help_text=_('Period, in years, for Revising Registrations'),
        null=True,
        blank=True,
    )

    class Meta:
        verbose_name = _('Rating')
        verbose_name_plural = _('Ratings')

    def __str__(self):
        return '{}'.format(
            self.titulo,
        )


class UserAssessment(models.Model):
    """
    Avaliação de riscos, conformidades, e fluxo de onboarding
    na visão do compliance.
    """
    user = models.OneToOneField(
        User,
        on_delete=models.PROTECT,
    )

    rating = models.ForeignKey(
        Rating,
        verbose_name=_('Rating'),
        on_delete=models.PROTECT,
        null=True,
        blank=True,
    )
    docs_revision_date = models.DateTimeField(
        verbose_name=_('Documents Revision Date'),
        null=True,
        blank=True,
    )

...

I need to count the number of records that are expired or will win in the next 30 days

"docs_revision_date" = informs date of last revision "rating.revision_period" = informs the period - in years - of revision

I want the records that have exceeded the deadline ( * 365 ) or That will burst in 30 days ( * 330 )

My last attempt was

    vencidos = len(UserAssessment.objects.filter(
        docs_revision_date__lte=make_aware(datetime.today())-timedelta(
            days='rating__revision_period' * 365), 
        docs_revision_date__lte=make_aware(datetime.today())-timedelta(
            days='rating__revision_period' * 330),

        )
    )

The problem is that I can’t put the period ('rating__revision_period') inside timedelta()

someone has some guidance, pfv?

Answer 1

Hi, what you are trying to do is not possible unless you perform the query previously, because you are trying to filter with your own result ('rating__revision_period'), this is not possible, at least that I know. I have been analyzing, there is no direct solution, except to iterate on all 'Rating' and filter by your id and use your revision_period. I share an example for you to analyze with the idea. In this case I am loading an array with output querysets.

new_result = []
rating_objects = Rating.objects.all()
for item in rating_objects:
    new_result.append(
        UserAssessment.objects.filter(
                    rating_id = item.id,
                    docs_revision_date__lte=datetime.now(tz=timezone.utc)-timedelta(days=365 * item.revision_period),
                    docs_revision_date__lte=datetime.now(tz=timezone.utc)-timedelta(days=330 * item.revision_period))
        )