I'm having trouble with both my serializer and models for a table using foreign keys. I have a view for my Cost table ( see below ) that when I query, I get the following output in about 300-400 ms :
[
{
"id": 12,
"hours1": 10,
"hours2": 0,
"hours3": 0,
"hours4": 0,
"date": "2021-07-12",
"employee": 14,
"job1": 417,
"job2": 671,
"job3": 671,
"job4": 671
},
{
"id": 13,
"hours1": 8,
"hours2": 0,
"hours3": 0,
"hours4": 0,
"date": "2021-07-12",
"employee": 10,
"job1": 411,
"job2": 671,
"job3": 671,
"job4": 671
}
]
The employee, job1, job2, job3, job4 fields are foreign key IDs that I wish to see their main/primary value for (in this case, names!). I've played around with a serializer to achieve this, however, the problem is that it takes about 90 seconds per query and keeps getting longer!
[
{
"id": 12,
"employee": {
"employee_name": "Person 1"
},
"job1": {
"No": "30201"
},
"job2": {
"No": "N/A"
},
"job3": {
"No": "N/A"
},
"job4": {
"No": "N/A"
},
"hours1": 10,
"hours2": 0,
"hours3": 0,
"hours4": 0,
"date": "2021-07-12"
},
{
"id": 13,
"employee": {
"employee_name": "Person 2"
},
"job1": {
"No": "30101"
},
"job2": {
"No": "N/A"
},
"job3": {
"No": "N/A"
},
"job4": {
"No": "N/A"
},
"hours1": 8,
"hours2": 0,
"hours3": 0,
"hours4": 0,
"date": "2021-07-12"
}
]
My schema and serializers:
class Employee(models.Model):
employee_name = models.CharField(max_length=200, unique=True)
employee_email = models.CharField(max_length=200)
employee_navID = models.CharField(max_length=200)
employee_department = models.CharField(max_length=200)
joining_date = models.DateField()
leaving_date = models.DateField()
weekly_salary = models.IntegerField(default=0)
car_allowance = models.IntegerField()
national_insurance = models.IntegerField()
pension = models.IntegerField()
created_at = models.DateField(auto_now_add=True)
@property
def status(self):
if(self.leaving_date >= date.today()):
return "active"
else:
return "inactive"
@property
def employee_cost(self):
return self.weekly_salary self.car_allowance self.national_insurance self.pension
class Meta:
verbose_name_plural = "Employees"
def __str__(self):
return self.employee_name
class ExcelJobsList(models.Model):
No = models.CharField(max_length=200)
Description = models.CharField(max_length=200)
Custom_No = models.CharField(max_length=200)
Sell_to_Name = models.CharField(max_length=200)
Status = models.CharField(max_length=200)
Person_Responsible = models.CharField(max_length=200)
Region_Code = models.CharField(max_length=200)
Market_Code = models.CharField(max_length=200)
Agreement_Form_Code = models.CharField(max_length=200)
Technology_Code = models.CharField(max_length=200)
secondappr_Code = models.CharField(max_length=200)
Search_Description = models.CharField(max_length=200)
Work_in_Progress_Status = models.CharField(max_length=200)
Job_Category = models.CharField(max_length=200)
Project_Manager = models.CharField(max_length=200)
Sales_Person = models.CharField(max_length=200)
Payment_Terms_Code = models.CharField(max_length=200)
First_Agreement_No = models.CharField(max_length=200)
No_of_Service_Agreements = models.CharField(max_length=200)
CRM_Reference = models.CharField(max_length=200)
class Meta:
verbose_name_plural = "Jobs"
def __str__(self):
return self.No
These are populated with a unique list of employees and jobs respectively. I then use to this to create a basic timesheet form where users can assign hours to a job (up to 4). This is posted to the below model, which has foreign keys to the employee and job table.
class Cost(models.Model):
employee = models.ForeignKey(
Employee, default=1, on_delete=SET_DEFAULT)
job1 = models.ForeignKey(ExcelJobsList, default=0, on_delete=SET_DEFAULT)
hours1 = models.IntegerField()
job2 = models.ForeignKey(
ExcelJobsList, default=0, on_delete=SET_DEFAULT, related_name="job2")
hours2 = models.IntegerField()
job3 = models.ForeignKey(
ExcelJobsList, default=0, on_delete=SET_DEFAULT, related_name="job3")
hours3 = models.IntegerField()
job4 = models.ForeignKey(
ExcelJobsList, default=0, on_delete=SET_DEFAULT, related_name="job4")
hours4 = models.IntegerField()
date = models.DateField()
This produces the follow output after I've selected the fields I wanted from a serializer:
class JobModelSerializer(serializers.ModelSerializer):
class Meta:
model = ExcelJobsList
fields = ['No']
class EmployeeModelSerializer(serializers.ModelSerializer):
class Meta:
model = Employee
fields = ['employee_name']
class CostModelSerializer(serializers.ModelSerializer):
employee = EmployeeModelSerializer()
job1 = JobModelSerializer()
job2 = JobModelSerializer()
job3 = JobModelSerializer()
job4 = JobModelSerializer()
class Meta:
model = Cost
fields = ('__all__')
class CostListFilter(filters.FilterSet):
class Meta:
model = Cost
fields = {
'employee': ['exact'],
}
class JoinedFilterCostList(generics.ListAPIView):
queryset = Cost.objects.filter()
serializer_class = CostModelSerializer
filter_backends = [DjangoFilterBackend]
filterset_class = CostListFilter
I've tried using to_field= 'xyz', however, this ends up returning null so either I'm missing something in my model or the serializer isn't set up correctly for it... or both!
Any help would be much really appreciated.
CodePudding user response：
The slowness is caused by having to hit the database 4 times per Cost instance to get all the related ExcelJobsList.
To avoid this and make it more efficient, you can use select_related to the related ExcelJobsList like this:
class JoinedFilterCostList(generics.ListAPIView):
...
queryset = Cost.objects.select_related('job1', 'job2', 'job3', 'job4')
...
These should in turn end up with just one query, with inner joins on the related ExcelJobsList.