Django filter queryset for each item

I cant think of a good logic myself so I hope that somebody could help me out.

The goal is this:

I have a date which a user can enter, a from_date, prognosis_hour, to_date and default_hour.

I need to filter from the employee_set each employer who has a from_date with his prognosis_hour within the range of the date which a user enters, to sum it with other employee hours, the twist is, when there are employees without from_date with prognosis_hours, then default_hour should be used.

Now I have this

    @property
    def prognosis(self):
        mylist = []
        for e in self.employee_set.exclude(prognosis_hours__isnull=True, from_date__isnull=True):
            for i in self.employee_set.filter(from_date__gte=self.entered_date, to_date__lte=self.entered_date):
                e.default_hours 
                mylist.append(i.prognosis_hours)
                mylist.append(e.default_hours)
                return mylist 

right now it filter everybody within the entered date and append it to the list, showing default and prognosis hours, but ignores everybody else with default hours

In the end it should just sum all default and prognosis hours together when prognosis hours are within range of the date today or the entered date from user

I know that the sum is missing and that I can append it together etc. it is copied from my debugging process

class work(models.Model):
      entered_date = DateField()


    @property
    def prognosis(self):
        mylist = []
        for e in self.employee_set.exclude(prognosis_hours__isnull=True, from_date__isnull=True):
            for i in self.employee_set.filter(from_date__gte=self.entered_date, to_date__lte=self.entered_date):
                e.default_hours 
                mylist.append(i.prognosis_hours)
                mylist.append(e.default_hours)
                return mylist 

class employee(models.Model:
     work_fk = models.ForeignKey(work, null=True, on_delete=models.SET_NULL)
     default_hours = models.IntegerField(default=0)
     from_date = models.DateField(null=True, blank=True)
     prognosis_hours = models.IntegerField(default=0)
     to_date = models.DateField(null=True, blank=True)

I am not sure about your logic, but i can imagine, that you want:

@property
def prognosis(self):
    query = Q(from_date__gte=self.entered_date) | Q(from_date__isnull=True)
    query &= Q(to_date__lte=self.entered_date) | Q(to_date__isnull=True)

    queryset = self.employee_set.annotate(prognosis_hours=Coalesce('prognosis_hours', default_hours).filter(query)
    return queryset.aggregate(Sum('prognosis_hours'))

the obj.prognosis should return summ of all prognosis_hours in filtered employee_set.

More here: https://docs.djangoproject.com/en/4.1/ref/models/database-functions/

p.s. The code has not been tested on a real project and may contain errors

Back to Top