Can Prefetch Related be used with the same model?

Question

Django

There is a model:

class Product(models.Model):
    name = models.CharField()
    group_id = models.CharField()
    is_main = models.BooleanField()

    def child(self):
        return Product.objects.filter(group_id=self.group_id)

On the page I take: and I bring it out.
products = Product.objects.filter(is_main=True)

{% for p in products %}
<ul>
    {% for c in p.child %}
        <li>{{ c.name }}</li>
    {% endfor %}
</ul>
{% endfor %}

Accordingly, for each such cycle, its own query is generated in the database. For a small amount, this is not atk critical, but if you increase the number of products, then the number of requests grows in an algebraic progression.
If there would be a FK or M2M dependency, then prefetch_related could be used. But that won't work here. I read something about Subquery, but I never dug up anything.
Is there any way to reduce the number of queries to the database?

Answer 1

As far as I know, such a feint with the ears is not provided by standard janga tools :)
But if you really want to get this effect, you can "simulate" it:

products = Product.objects.filter(is_main=True)
groups = [item.group_id for item in products]

children = Product.objects.filter(group_id__in=groups).order_by('group_id')
children = {
    grouper: elements for grouper, elements in
    groupby(children, key=lambda x: x.group_id)
}

for product in products:
    product.children = children.get(product.group_id, [])

{% for p in products %}
    <ul>
        {% for c in p.children %}
            <li>{{ c.name }}</li>
        {% endfor %}
    </ul>
{% endfor %}

PS I will leave the question of the need for these manipulations to your discretion :)
PPS did not check the code.

Answer 2

You can use something, only you do not have a connection between the model and itself. In prefetch_ related , the key is relation.
Here's what you need:

main_products = Product.objects.filter(is_main=True)
all_products = Product.objects.filter(
    Q(is_main=True) | Q(group_id__in=main_products.values_list("group_id", flat=True))
)

Here's a nonsensical example that shows that subqueries actually work:

str(Review.objects.filter(Q(user_id__gt=4000) | Q(user_id__in=Review.objects.values_list('user_id', flat=True))).query)
Out[10]: 'SELECT "reviews_review"."id", "reviews_review"."user_id", "reviews_review"."email", "reviews_review"."rating", "reviews_review"."comment", "reviews_review"."datetime" FROM "reviews_review" WHERE ("reviews_review"."user_id" > 4000 OR "reviews_review"."user_id" IN (SELECT U0."user_id" AS Col1 FROM "reviews_review" U0))'