Как использовать вложенный ArrayAgg в Django?
Это мои модели
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)
Это мой набор запросов для получения всех табелей учета рабочего времени вместе с оборудованием
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()
)
Это набор запросов, в котором я пытаюсь получить все табели учета рабочего времени вместе со всем оборудованием, и каждое оборудование должно иметь все вложения оборудования, если оно существует.
Но это не помогло. Любая помощь будет высоко оценена. Или есть ли какой-нибудь другой способ добиться того же самого.
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()
)
Вот ошибка, которую я получаю при выполнении приведенного выше кода.
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