code.py

# models.py

class ProductionOrder(models.Model):

    step = models.OneToOneField(
        "order.OrderStep",
        on_delete=models.DO_NOTHING,
        verbose_name=_("Step"),
        related_name="po_productionorder",
    )
    comment = models.TextField(verbose_name=_("Comment"), null=True, blank=True)
    created_at = models.DateTimeField(verbose_name=_("Created At"), auto_now_add=True)
    created_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.DO_NOTHING,
        verbose_name=_("Created By"),
        related_name="po_createdby",
        null=True,
        blank=True,
    )
    protocol = models.TextField(verbose_name=_("Protocol"), null=True, blank=True)

    objects = ProductionOrderManager()
    objects_clear = ProductionOrderClearManager()
    rel_manager = ProductionOrderRelatedManager()


class ProductionOrderJob(models.Model):

    production_order = models.ForeignKey(
        ProductionOrder,
        on_delete=models.CASCADE,
        verbose_name=_("Production Order"),
        related_name="poj_productionorder",
    )
    production_job = models.ForeignKey(
        Job,
        on_delete=models.DO_NOTHING,
        verbose_name=_("Production Job"),
        related_name="poj_productionjob",
        null=True,
        blank=True,
    )
    technician = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.DO_NOTHING,
        verbose_name=_("Technician"),
        related_name="poj_technician",
        null=True,
        blank=True,
        limit_choices_to={"role__is_technician": True},
    )


# query

def get_production_orders_prefetch_salary(
        self,
        technician_id: int,
        pk_list
    ) -> QuerySet:

    count_subquery = (
        ProductionOrderJob.objects_clear.filter(
            technician_id=technician_id,
            production_job=OuterRef("production_job")
        ).count()
    )

    return (
        ProductionOrder.objects_clear.filter(pk__in=pk_list)
        .prefetch_related(
            Prefetch(
                "poj_productionorder",
                queryset=ProductionOrderJob.objects_clear.filter(
                    technician_id=technician_id
                )
                .annotate(
                    created_count=
                        Subquery(count_subquery),
                    # Count(
                        # Case(
                        #     When(
                        #         production_job=F("production_job"),
                        #         then=1
                        #     ),
                        #     output_field=IntegerField()
                        # )
                    # )
                ),
            ),
        )
    )