How to correctly make a query to database with Django ORM?

Question

I have stuck in how to write query to avoid for loops

Calculation of the number of sick days during the period of the first creation of the sick object for an employee + 365 days (That is, during the period of the year after the first creation of the object) After the year, the counter should reset and after the iteration will go from the first creation of the object after the date (the first sick object + 365 days)

I have two models

class Employee(Model):
    birth_name = CharField(_('Nom De Naissance'), max_length=120)
    last_name = CharField(max_length=120, verbose_name="Nom D'Usage")
    first_name = CharField(_('Prénom'), max_length=120, unique=True)


class Sick(Model):
    class TypeSick(Enum):
        Initial = 'Initial'
        Prolongation = 'Prolongation'
        Accident_Travail = 'Accident Travail'
        Accident_Trajet = 'Accident Trajet'
        Congés_Maternités = 'Congés Maternités'
        AUTRES = 'Autres'

    type_sick = CharField(
        max_length=120,
        choices=choice_handler(TypeSick),
    )

    start_date = DateField(
        editable=True, null=False, blank=False
    )
    end_date = DateField(
        editable=True, null=True, blank=True
    )

    after_year = DateField(editable=True, null=True, blank=True)

    employee = ForeignKey(
        Employee,
        on_delete=SET_NULL,
        null=True,
        blank=True,
        verbose_name='Employe',
        related_name='sick',
    )

Here what i've done for get a first sick object of every employee and count his end year +365 days

def save_sick_end_year(
    request: HttpRequest,
    sick_model: Model,
    type_sick: str,
    employee_model: Model,
) -> tuple[
    QuerySet[Model, dict[str, dt.date]],
    QuerySet[Model, dict[str, dt.date]],
]:
    employees = employee_model.objects.all()

    sick_subquery = sick_model.objects.filter(
        employee=OuterRef('pk'), type_sick=type_sick
    ).order_by('start_date')

    _sick = employees.annotate(
        _start=Subquery(sick_subquery.values('start_date')[:1]),
        _end=ExpressionWrapper(
            F('_start') + dt.timedelta(days=365).days,
            output_field=DateField(),
        ),
    ).values_list('first_name', 'last_name', '_start', '_end')

    return _sick

output: <QuerySet [('AAA', 'BBB', datetime.date(2024, 5, 5), datetime.date(2025, 5, 5)), ('CCC', 'XXX', datetime.date(2024, 6, 6), datetime.date(2025, 6, 6))]>

Problem now to find all sick objects between this two dates _start and _end

Answer 1

Here my modified solution to calculate all sick days in defined period

    sick_subquery = (
        sick_model.objects.filter(employee=OuterRef('pk'), type_sick=type_sick)
        .annotate(_date=Coalesce('employee__is_year', 'start_date'))
        .order_by(
            Case(
                When(
                    employee__is_year__isnull=False,
                    then=F('employee__is_year'),
                ),
                default=F('start_date'),
            )
        )
        .values('_date')[:1]
    )

    employees = (
        employee_model.objects.annotate(start=Subquery(sick_subquery))
        .values(
            'start',
        )
        .annotate(
            end=ExpressionWrapper(
                F('start') + dt.timedelta(days=365),
                output_field=DateField(),
            )
        )
        .values('start', 'end')
        .annotate(
            total_days=Sum(
                (
                    (F('sick__end_date') - F('sick__start_date'))
                    + dt.timedelta(days=1)
                ),
                output_field=DurationField(),
                filter=Q(
                    sick__start_date__gte=F('start'),
                    sick__end_date__lte=F('end'),
                ),
            )
        )
        .annotate(total_days=Coalesce(Extract('total_days', 'day'), Value(0)))
        .values(
            'uuid',
            'first_name',
            'last_name',
            'is_year',
            'start',
            'end',
            'total_days',
        )
        .order_by('-total_days')
    )