Prefetch related starting from a single object - geting first in second prefetch and count and order

Question

I have 3 Models Product,Company Categories.

class Product(Meta):
    categories = models.ManyToManyField(Category)
    company = models.ForeignKey(Company, related_name='products', on_delete=models.CASCADE)
updated_at = models.DateTimeField(auto_now_add=False, auto_now=True)

I need:

• to get all the products of a company
• show the product first category
• count the number products per company and show
• order products by reverse updated_at

I start from:

1. Company.objects.get(pk=company_pk).prefetch_related('products')

will give me an error, because get returns an object:

class CompanyProductListView(ListView):
 model = Company
 template_name_suffix = '_company_list'

def get_queryset(self):
    company_pk = self.kwargs.get('pk')
    return Company.objects.get(pk=company_pk).prefetch_related('products')

get without prefetch works.

2. return Company.objects.filter(pk=company_pk).prefetch_related('products')

there is no error, but in template:

 {% for company in company_list %}
        {{ company.name}}
    {% endfor %}

I loop even is one, but doesn't show me anything.

Besides that I need to attach first category to each product, and count the number of products

I'm thinking on access something like this:

{{company.name}}

{% for product in company.products %}
   {{ product.name }}
   {{ product.category }}

Answer 1

This query will get a little complicated, but should help you solve your issue.

PS: I haven't tested this but should mostly work. Will take a deeper look once I get some more time.

First we get the company we want:

company = Company.objects.get(pk=company_pk)

Then we fetch all the first categories for all products, it can be done by using this question as a guide:

first_categories = Category.objects.order_by('product__id', '-id').distinct('product__id')

Now we use the first_categories to use to limit the amount of data we prefetch (giving this a different perspective, we will query the Product model instead of the Company model)

product_list = Products.objects.filter(company=company).prefetch_related(
    Prefetch('categories', queryset=first_categories)
)

---

def get_queryset():
    company_pk = ...
    company = ...
    first_categories = ...
    product_list = ...
    return product_list