I want to filter the fields retrieved in a query and get the reverse foreign key using the related_name in a child model using prefetch_related() for the sake of performance optimization when reaching a huge number of database records.
I have the following "Seller" Model:
class SellerQuerySet(models.QuerySet):
def annotate_seller_salary(self):
return self.annotate(seller_salary=F('points')*100)
class SellerManager(models.Manager):
use_for_related_fields = True
def annotate_seller_salary(self):
return self.get_queryset().annotate_seller_salary()
def get_queryset(self):
return SellerReviewQuerySet(model=self.model, using=self._db)
class Seller(models.Model):
name= models.CharField(max_length=50)
points= models.IntegerField(default=0)
address= models.TextField(max_length=100)
And "Product" Model:
class Product(models.Model):
name= models.CharField(max_length=50)
quantity= models.IntegerField(default=0)
seller= models.ForeignKey(Seller, on_delete=models.CASCADE, related_name="seller_products")
I'm trying to get all the products grouped by the seller. But the issue is that If I retrieved the products first and then tried to group it by the seller, I will not be able to retrieve the annotation function for the "Seller" model along with it.
So, I would need to start by retrieving the "Seller" object with its annotations and then use prefetch_related() and Prefetch() to get the Products using the related_name for the seller foreign key. BUT! The problem is, I don't want to get all the "Seller" model fields. I ONLY need the points field and the annotation attribute seller_salary along with the "Product" objects that are associated with each "Seller" object.
I've tried several to ways to filter the selected fields of the "Seller" model but nothing worked.
I've tried using values() as in the following:
sellers=Seller.objects.all().annotate_seller_salary().prefetch_related(Prefetch('seller_products', Product.objects.filter(points__gt=0).order_by('id'))).values('points', 'seller_salary')
But whenever I try to access the "Product" objects as usual:
for product in sellers.seller_products.all()
it shows me an error saying that "dict object has no attribute 'seller_products'"
Alternatively, using only() instead, as in the following:
sellers=Seller.objects.all().annotate_seller_salary().prefetch_related(Prefetch('seller_products', Product.objects.filter(points__gt=0).order_by('id'))).values('points', 'seller_salary')
shows me an error saying that "Seller has no field named 'seller_salary'"
Is there any idea how to fix this? Or is there another solution that can help me reach the same approach with the same performance?
Remove 'seller_salary'
from the fields. Annotations are added automatically, so:
sellers = Seller.objects.annotate_seller_salary().prefetch_related(
Prefetch(
'seller_products',
Product.objects.filter(points__gt=0).order_by('id')
)
).only('points')