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.
If you need the field to be in the queryset, annotate might be unavoidable. Constructing the subqueries is easier if you break them up.
I'm a little rusty on the subquery creation but this should be pretty close. You might need to move the .values() declarations from the subquery definition and into the Subquery expression of the parent query if these don't work out of the box.
# Extract `name` from the client
client_query = Client.objects.filter(id=OuterRef('client_id')).values('name')
rent_query = Rent.objects
# Tack on `client_name` from the first subquery
.annotate(client_name=Subquery(client_query))
# Select the relevant Rent objects and pull out `client_name`
.filter(id=OuterRef('rent_id')).values('client_name')
# Tack on `client_name` from the second subquery to the final queryset
queryset = RentSchedule.objects.annotate(client_name=Subquery(rent_query))