How to use utilize nested ArrayAgg in Django?

These are my models

class TimeSheet(CommonModel):
    employee = models.ForeignKey(
        "employee.Employee", related_name="timesheets", on_delete=models.PROTECT
    )
    project = models.ForeignKey(
        "core.Project", related_name="employee_timesheets", on_delete=models.PROTECT
    )
    timesheet_date = models.DateField()
    clock_in_time = models.DateTimeField()
    clock_out_time = models.DateTimeField(null=True)


class TimesheetEquipment(CommonModel):
    equipment = models.ForeignKey(
        "equipment.Equipment",
        on_delete=models.PROTECT,
        related_name="timesheet_equipments",
    )
    resource_code = models.CharField(max_length=150)
    resource_name = models.CharField(max_length=511)
    rate = models.DecimalField(
        max_digits=30, decimal_places=decimal.ACCEPTED_DECIMAL_PLACE
    )
    unit = models.CharField(max_length=63, default="Hour")
    timesheet = models.ForeignKey(
        "timesheet.Timesheet",
        on_delete=models.CASCADE,
        related_name="timesheet_equipments",
    )
    equipment_attachments = models.ManyToManyField(
        "costcode.ResourceCode",
        related_name="timesheet_equipments",
        blank=True,
        through="timesheet.TimesheetEquipmentAttachment",
        help_text="Other Resources to be attached with this requirement",
    )
 

class TimesheetEquipmentAttachment(models.Model):
    timesheetequipment = models.ForeignKey(
        TimesheetEquipment,
        on_delete=models.CASCADE,
        related_name="timesheetequipmentattachments",
    )
    resource = models.ForeignKey(
        "resource_management.CostSheetResource",
        related_name="timesheetequipmentattachments",
        on_delete=models.PROTECT,
        null=True,
    )
    resource_code = models.CharField(max_length=150)
    resource_name = models.CharField(max_length=150)

This is my queryset to get all the timesheet along with its equipments

from django.db.models.functions import JSONObject
from django.contrib.postgres.aggregates import ArrayAgg

timesheets = (
            TimeSheet.objects.filter(project_id=project_id, timesheet_date=date)
            .select_related("employee")
            .prefetch_related(
                "timesheet_equipments",
                "timesheet_equipments__timesheetequipmentattachments",
            )
            .values("id")
            .annotate(
                timesheet_date=F("timesheet_date"),
                clock_in_time=F("clock_in_time"),
                clock_out_time=F("clock_out_time"),
                timesheet_equipments=ArrayAgg(
                    JSONObject(
                        equipment=F("timesheet_equipments__equipment_id"),
                        equipment_name=F(
                            "timesheet_equipments__equipment__equipment_name"
                        ),
                        resource_code=F("timesheet_equipments__resource_code"),
                        resource_name=F("timesheet_equipments__resource_name"),
                        rate=F("timesheet_equipments__rate"),
                        unit=F("timesheet_equipments__unit"),
                       
                      
                    ),
                    distinct=True,
                ),
            )
            .values(
                "id",
                "timesheet_date"
                "clock_in_time",
                "clock_out_time",
                "timesheet_equipments",
            )
            .order_by("-timesheet_date")
            .distinct()
        )

this is the queryset that i am trying to get all the timesheets along with its all equipments and each equipments should have all of there equipment attachments if exists.

But this did not worked. Any help would be highly appreciated. Or is there any other way to achieve the same.

from django.db.models.functions import JSONObject
from django.contrib.postgres.aggregates import ArrayAgg

timesheets = (
            TimeSheet.objects.filter(project_id=project_id, timesheet_date=date)
            .select_related("employee")
            .prefetch_related(
                "timesheet_equipments",
                "timesheet_equipments__timesheetequipmentattachments",
            )
            .values("id")
            .annotate(
                timesheet_date=F("timesheet_date"),
                clock_in_time=F("clock_in_time"),
                clock_out_time=F("clock_out_time"),
                timesheet_equipments=ArrayAgg(
                    JSONObject(
                        equipment=F("timesheet_equipments__equipment_id"),
                        equipment_name=F(
                            "timesheet_equipments__equipment__equipment_name"
                        ),
                        resource_code=F("timesheet_equipments__resource_code"),
                        resource_name=F("timesheet_equipments__resource_name"),
                        rate=F("timesheet_equipments__rate"),
                        unit=F("timesheet_equipments__unit"),
                      equipment_attachments=ArrayAgg(
                            JSONObject(
                                resource_code=F(
                                    "timesheet_equipments__timesheetequipmentattachments__resource_code"
                                ),
                                resource_name=F(
                                    "timesheet_equipments__timesheetequipmentattachments__resource_name"
                                ),
                                rate=F(
                                    "timesheet_equipments__timesheetequipmentattachments__rate"
                                ),
                            ),
                            distinct=True,
                        ),
                      
                    ),
                    distinct=True,
                ),
            )
            .values(
                "id",
                "timesheet_date"
                "clock_in_time",
                "clock_out_time",
                "timesheet_equipments",
            )
            .order_by("-timesheet_date")
            .distinct()
        )

here is the error that I am getting when running above code.

FieldError: Cannot compute None('JSONObject(Value(equipment), F(timesheet_equipments__equipment_id), Value(equipment_name), F(timesheet_equipments__equipment__equipment_name), Value(resource_code), F(timesheet_equipments__resource_code), Value(resource_name), F(timesheet_equipments__resource_name), Value(rate), F(timesheet_equipments__rate), Value(unit), F(timesheet_equipments__unit), Value(equipment_duration), F(timesheet_equipments__equipment_duration), Value(total_duration), F(timesheet_equipments__total_duration), Value(note), F(timesheet_equipments__note), Value(wet_hire), F(timesheet_equipments__wet_hire), Value(equipment_attachments), ArrayAgg(JSONObject(Value(resource_code), F(timesheet_equipments__timesheetequipmentattachments__resource_code), Value(resource_name), F(timesheet_equipments__timesheetequipmentattachments__resource_name), Value(rate), F(timesheet_equipments__timesheetequipmentattachments__rate)), distinct=True))'): 'JSONObject(Value(equipment), F(timesheet_equipments__equipment_id), Value(equipment_name), F(timesheet_equipments__equipment__equipment_name), Value(resource_code), F(timesheet_equipments__resource_code), Value(resource_name), F(timesheet_equipments__resource_name), Value(rate), F(timesheet_equipments__rate), Value(unit), F(timesheet_equipments__unit), Value(equipment_duration), F(timesheet_equipments__equipment_duration), Value(total_duration), F(timesheet_equipments__total_duration), Value(note), F(timesheet_equipments__note), Value(wet_hire), F(timesheet_equipments__wet_hire), Value(equipment_attachments), ArrayAgg(JSONObject(Value(resource_code), F(timesheet_equipments__timesheetequipmentattachments__resource_code), Value(resource_name), F(timesheet_equipments__timesheetequipmentattachments__resource_name), Value(rate), F(timesheet_equipments__timesheetequipmentattachments__rate)), distinct=True))' is an aggregate

Back to Top