1

I have a model with a field datetime as reference date and a field integer that holds the number of days to count from reference date, I need to filter out the rows whos reference_date + days is lesser than current date in django orm. I have tried with RawSQL, delegating the filter to mysql, but i need to access a column of the row, and i don't know how to include an F expresion inside a RawSql expresion, i tried joining strings but it doesn't work. Included my model description.

class ActionData(models.Model):
    properties = models.ManyToManyField(Property, through='ActionProperties')
    action = models.ForeignKey(Action, on_delete=models.CASCADE)
    description = models.TextField(null=True)
    days = models.IntegerField(default=0)
    promocioned = models.BooleanField(default=False)
    reference_date = models.DateTimeField(null=True)
    modified_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)

    def __str__(self):
        return str(self.pk) + self.action.name
Alejandro
  • 51
  • 6
  • What is `number_days` and `current_date`? Are those singular values or fields on this model (maybe days) or related models? If they are on related models, please include those as well. – schillingt May 21 '20 at 18:10
  • Sorry, current_date is the actual date as provided by the system, number_days in the model is days field, i'll edit the question. Also i think i solve the problem building the queryset like this: `qset = ActionData.objects.filter(reference_date__lt = RawSQL('DATE_SUB(CURDATE(), INTERVAL %s DAY)', [F('days')]))` but i'm not sure yet. Thanks – Alejandro May 21 '20 at 18:20

1 Answers1

0

This should work for you:

ActionData.objects.filter(
    reference_date__lte=timezone.now() - (timedelta(days=1) * F('days'))
)
schillingt
  • 13,493
  • 2
  • 32
  • 34
  • Thanks for your help, sorry for the delay in the response, i got some errors using your answer: ` (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* `portal_actiondata`.`days`)))' at line 1")`, also maybe i'm using the wrog timezone object but the method now() looks missing, i changed timezone for datetime, and then i start receiving the error below. I will continue using the solution in my comment, looks like is working fine. Thanks again. – Alejandro Jun 05 '20 at 16:05