I am required to include a field present in a parent table to a grandchild table. I have to form a queryset to achieve the mentioned to return list of records for my mobile application. Refer below my models to have a clear picture.
#models.py
class Client(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=100)
contact = models.CharField(max_length=10, unique=True)
email = models.EmailField(null=True)
address = models.TextField()
modified_at = models.DateTimeField(auto_now=True)
created_at = models.DateTimeField(auto_now_add=True)
class Meta:
db_table = 'clients'
ordering = ['id']
verbose_name = 'Client'
verbose_name_plural = 'Clients'
def __str__(self):
return str(self.id) + ". " + self.name
class Rent(models.Model):
id = models.AutoField(primary_key=True)
address = models.TextField()
rent_amount = models.IntegerField()
deposit_amount = models.IntegerField()
rent_date = models.DateField()
document = models.TextField(null=True)
remarks = models.TextField(null=True)
created_at = models.DateTimeField(auto_now_add=True)
modified_at = models.DateTimeField(auto_now=True)
client_id = models.IntegerField()
class Meta:
db_table = 'rent'
verbose_name = 'Rent'
verbose_name_plural = 'Rents'
def __str__(self):
return self.id
def get_client_name(self):
client = Client.objects.get(pk=self.client_id)
return client.name
class RentSchedule(models.Model):
id = models.AutoField(primary_key=True)
rent_due_date = models.DateField()
paid_amount = models.IntegerField(default=0)
remaining_amount = models.IntegerField()
payment_status = models.IntegerField(choices=[(0, 'Unpaid'), (1, 'Paid')], default=0)
created_at = models.DateTimeField(auto_now_add=True)
rent_id = models.IntegerField()
class Meta:
db_table = 'rent_schedule'
verbose_name = 'Rent Schedule'
verbose_name_plural = 'Rent Schedule'
def __str__(self):
return self.id
def get_client_name(self):
rent = Rent.objects.get(pk=self.rent_id)
client = Client.objects.get(pk=rent.client_id)
return client.name
Below is my serializer class.
#serializers.py
class RentListSerializer(serializers.ModelSerializer):
client_name = serializers.CharField(source='get_client_name', required=False)
remaining_amount = serializers.IntegerField(read_only=True)
payment_status = serializers.ChoiceField(choices=[0, 1], default=0, write_only=True, error_messages={'invalid_choice': 'Options are 0 or 1'})
due_date = serializers.DateField(format="%d-%m-%Y", source='rent_due_date', read_only=True)
date_filter = serializers.DateField(input_formats=['%m-%Y'], default=datetime.now().strftime('%m-%Y'), write_only=True, required=False)
sort_by_client = serializers.ChoiceField(choices=["asc", "desc"], write_only=True, required=False, error_messages={'invalid_choice': 'Options are asc or desc'})
sort_by_due_date = serializers.ChoiceField(choices=["asc", "desc"], write_only=True, required=False, error_messages={'invalid_choice': 'Options are asc or desc'})
sort_by_remaining_amount = serializers.ChoiceField(choices=["asc", "desc"], write_only=True, required=False, error_messages={'invalid_choice': 'Options are asc or desc'})
class Meta:
model = RentSchedule
fields = ['id', 'client_name', 'paid_amount', 'remaining_amount', 'due_date', 'payment_status', 'date_filter', 'sort_by_client', 'sort_by_due_date', 'sort_by_remaining_amount']
def to_representation(self, instance):
representation = super().to_representation(instance)
representation['payment_status'] = "Paid" if instance.remaining_amount == 0 else "Pending"
representation.pop('due_date') if instance.remaining_amount == 0 else None
return representation
Now, finally my class based view is showcased below in which queryset is to be modified.
#views.py
class RentList(GenericAPIView):
authentication_classes = [TokenAuthentication]
permission_classes = [IsAuthenticated]
queryset = RentSchedule.objects.all()
serializer_class = RentListSerializer
class CustomPagination(PageNumberPagination):
page_size = 100
page_size_query_param = 'page_size'
max_page_size = 100
def get_paginated_response(self, data):
return Response({
'statusCode': 200,
'records': self.page.paginator.count,
'data': data,
'current_page': self.page.number,
'total_pages': self.page.paginator.num_pages
})
pagination_class = CustomPagination
def initial(self, request, *args, **kwargs):
super().initial(request, *args, **kwargs)
self.serializer = self.get_serializer(data=request.data)
if not self.serializer.is_valid():
raise ValidationError(detail=self.serializer.errors)
def filter_queryset(self, queryset, request):
serializer = self.serializer_class()
payment_status = request.data.get('payment_status')
client_name = request.data.get('client_name')
date_filter = request.data.get('date_filter')
sort_by_client = request.data.get('sort_by_client')
sort_by_due_date = request.data.get('sort_by_due_date')
sort_by_remaining_amount = request.data.get('sort_by_remaining_amount')
"""
This is to be modified as below is an incorrect join ORM.
queryset = queryset.annotate(
client_name=First(
Subquery(
Client.objects.filter(
id=Subquery(
Rent.objects.filter(
id=Subquery(
RentSchedule.objects.filter(
rent_id=OuterRef('id')
).values('rent_id')
)
).values('client_id')
)
).values('name')
)
)
)
"""
date_filter = date_filter if date_filter else serializer.fields['date_filter'].get_default()
month, year = date_filter.split('-')
queryset = queryset.filter(rent_due_date__month=month, rent_due_date__year=year)
payment_status = payment_status if payment_status is not None else serializer.fields['payment_status'].get_default()
queryset = queryset.filter(payment_status=payment_status)
if client_name:
queryset = queryset.filter(rent_id__in=Rent.objects.filter(client_id__in=Client.objects.filter(name__icontains=client_name).values('id')).values('id'))
if sort_by_client:
if sort_by_client.lower() == 'asc':
queryset = queryset.order_by('client_name')
elif sort_by_client.lower() == 'desc':
queryset = queryset.order_by('-client_name')
if sort_by_due_date:
if sort_by_due_date.lower() == 'asc':
queryset = queryset.order_by('rent_due_date')
elif sort_by_due_date.lower() == 'desc':
queryset = queryset.order_by('-rent_due_date')
if sort_by_remaining_amount:
if sort_by_remaining_amount.lower() == 'asc':
queryset = queryset.order_by('remaining_amount')
elif sort_by_remaining_amount.lower() == 'desc':
queryset = queryset.order_by('-remaining_amount')
return queryset
def post(self, request, *args, **kwargs):
queryset = self.filter_queryset(self.get_queryset(), request)
paginator = self.pagination_class()
paginated_queryset = paginator.paginate_queryset(queryset, request, view=self)
serializer = self.get_serializer(paginated_queryset, many=True)
return paginator.get_paginated_response(serializer.data)
def handle_exception(self, exc):
if isinstance(exc, ValidationError):
response = Response(
data={
"status_code": 400,
"message": exc.detail
},
status=400
)
return response
return super().handle_exception(exc)
My objective is to include name field from Client model which is the grandparent model to the queryset in mentioned in the comments. I want it to go by alias client_name such that it appears in the records by that key.
queryset = RentSchedule.objects.all()
Since, the models RentSchedule and Rent don’t have a foreignkey datatype due to my project limitations, I couldn’t use queryset’s select_related method to achieve same. I can only think of annonate method which would require complex subquery.
How do I achieve this so I can sort the queryset via client_name field in ‘asc’ or ‘desc’ order?
Thank you all in advance.