Django模板减少循环重复，减少sqlite3数据库查询

Question

我发现自己在同一组数据中反复循环，多次访问数据库以实现在一个模板上显示正确的数据，代码如下：

<!-- item images and thumbnails -->
    <div class="row">

        <div class="col-12 col-sm-8">
            <div id="item{{item.pk}}Carousel" class="carousel slide" data-ride="carousel">

                <ol class="carousel-indicators">
                    {% for image in item.itemimage_set.all %}
                    <li data-target="#item{{item.pk}}Carousel" data-slide-to="{{forloop.counter0}}" 
                            {% if forloop.first %} class="active" {% endif %}></li>
                    {% endfor %}
                </ol>

                <div class="carousel-inner shadow-lg rounded-sm">

                    {% for image in item.itemimage_set.all %}
                    <div class="carousel-item {% if forloop.first %} active {% endif %}">
                        <a href="#itemImageModal" data-toggle="modal"><img src="{{image.image.url}}" class="d-block w-100" alt="..."></a>
                    </div>
                    {% endfor %}

                </div>

                {% if item.itemimage_set.count > 1 %}
                <a class="carousel-control-prev" href="#item{{item.pk}}Carousel" role="button" data-slide="prev">
                    <span class="carousel-control-prev-icon" aria-hidden="true"></span>
                    <span class="sr-only">Previous</span>
                </a>
                <a class="carousel-control-next" href="#item{{item.pk}}Carousel" role="button" data-slide="next">
                    <span class="carousel-control-next-icon" aria-hidden="true"></span>
                    <span class="sr-only">Next</span>
                </a>
                {% endif %}

            </div>

        </div>


        <div class="pl-sm-0 col-12 col-sm-4 d-flex flex-wrap align-content-start">
            {% for image in item.itemimage_set.all %}
            <div class="col-4 
            {% if item.itemimage_set.count > 3 %}
            col-sm-6 
            {% else %}
            col-sm-8 
            {% endif %} 
            mt-2 px-1 mt-sm-0 pb-sm-2 pt-sm-0 mb-0">
                <img src="{{image.image.url}}" alt="" class="col-12 p-0 rounded-sm shadow-sm"
                    data-target="#item{{item.pk}}Carousel" data-slide-to="{{forloop.counter0}}">
            </div>
            {% endfor %}
        </div>

    </div>
<!-- /item images and thumbnails -->

上面的代码呈现项目的itemimage引导旋转木马，并且在同一页面上，呈现一个额外的旋转木马模式：

<!-- itemImageModal -->
<div class="modal fade" id="itemImageModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalCenterTitle"
    aria-hidden="true">
    <div class="modal-dialog modal-dialog-centered col-12 col-md-8 modal-lg" role="document">
        <div class="modal-content">
            <div class="col-12 px-0">
                <div id="itemImage{{item.pk}}Carousel" class="carousel slide" data-ride="carousel">

                    <ol class="carousel-indicators">
                        {% for image in item.itemimage_set.all %}
                        <li data-target="#itemImage{{item.pk}}Carousel" data-slide-to="{{forloop.counter0}}" 
                                {% if forloop.first %} class="active" {% endif %}></li>
                        {% endfor %}
                    </ol>

                    <div class="carousel-inner shadow-lg rounded-sm">

                        {% for image in item.itemimage_set.all %}
                        <div class="carousel-item {% if forloop.first %} active {% endif %}">
                            <a href="#itemImageModal" data-toggle="modal"><img src="{{image.image.url}}" class="d-block w-100" alt="..."></a>
                        </div>
                        {% endfor %}

                    </div>

                    {% if item.itemimage_set.count > 1 %}
                    <a class="carousel-control-prev" href="#itemImage{{item.pk}}Carousel" role="button" data-slide="prev">
                        <span class="carousel-control-prev-icon" aria-hidden="true"></span>
                        <span class="sr-only">Previous</span>
                    </a>
                    <a class="carousel-control-next" href="#itemImage{{item.pk}}Carousel" role="button" data-slide="next">
                        <span class="carousel-control-next-icon" aria-hidden="true"></span>
                        <span class="sr-only">Next</span>
                    </a>
                    {% endif %}

                </div>

            </div>
        </div>
    </div>
</div>

---

数据结构：

class Item(models.Model):
    name = ... etc.

class ItemImage(models.Model):
    item = models.ForeignKey(Item, on_delete=models.CASCADE)
    image = models.ImageField(upload_to='itemimages', null=True, blank=True)

如您所见，模板中有5个forloop循环，这导致数据库对同一组数据进行频繁查询。

---

我所尝试的：

我试图用{% for image in item.load_related_itemimage %}替换{% for image in item.itemimage_set.all %}，在models.py中：

class Item(models.Model):
    name = ...

    def load_related_itemimage(self):
        return self.itemimage_set.prefetch_related('image')

此提示错误：

'image' does not resolve to an item that supports prefetching - this is an invalid parameter to prefetch_related().

实际上，我对django很陌生，不知道如何使用select_related或prefetch_related，但到目前为止，我已经使用了它们，并设法将数据库查询从150减少到30+。我认为，由于上述愚蠢的循环，频率可以进一步降低，正如你所看到的

---

转发页面的调试工具栏数据：

SELECT "appname_itemimage"."id",
       "appname_itemimage"."item_id",
       "appname_itemimage"."image"
  FROM "appname_itemimage"
 WHERE "appname_itemimage"."item_id" = '19'
  5 similar queries.   Duplicated 5 times.

5 similar queries. Duplicated 5 times.不好，对吗

---

view.py

class ItemDetailView(DetailView):
    '''display an individual item'''
    model = Item
    template_name = 'boutique/item.html'

---

感谢@Iain Shelvington的回答，他对上述代码的解决方案非常有效，这个怎么样：

我认为它们是相关的，所以我没有把它们分开来提出另一个问题——如果item本身是一个forloop呢？在这种情况下如何使用prefetch_related？谢谢

{% for item in subcategory.item_set.all %}

<a href="{{ item.get_item_url }}"><img src="{{ item.itemimage_set.first.image.url }}"></a>


{% endfor %}

因为在subcateogry.item_set循环时我需要访问item.itemimage_set，这是一个比以前更糟糕的问题，因为它在加载itemimage时会引发19次重复

此模板由ListView呈现

class CategoryListView(ListView):
    '''display a list of items'''
    model = Category
    # paginate_by = 1
    template_name = 'boutique/show_category.html'
    context_object_name = 'category_shown'

    def get_queryset(self):
        qs = super().get_queryset().get_categories_with_item()
        self.gender = self.kwargs.get('gender')  # reuse in context
        gender = self.gender
        request = self.request

        # fetch filter-form data
        self.category_selected = request.GET.get('category_selected')
        self.brand_selected = request.GET.get('brand_selected')
        self.min_price = request.GET.get('min_price')
        self.max_price = request.GET.get('max_price')

        if gender == 'women':
            self.gender_number = 1
        elif gender == 'men':
            self.gender_number = 2
        else:
            raise Http404

        get_category_selected = Category.objects.filter(
            gender=self.gender_number, name__iexact=self.category_selected).first()
        category_selected_pk = get_category_selected.pk if get_category_selected else None

        get_subcategory_selected = SubCategory.objects.filter(
            category__gender=self.gender_number, name__iexact=self.category_selected).first()
        subcategory_selected_pk = get_subcategory_selected.pk if get_subcategory_selected else None

        category_pk = category_selected_pk if category_selected_pk else self.kwargs.get(
            'category_pk')
        subcategory_pk = subcategory_selected_pk if subcategory_selected_pk else self.kwargs.get(
            'subcategory_pk')

        # print('\nself.kwargs:\n', gender, category_pk, subcategory_pk)

        if gender and not category_pk and not subcategory_pk:
            qs = qs.get_categories_by_gender(gender)
            # print('\nCategoryLV_qs_gender= ', '\n', qs, '\n', gender, '\n')
            return qs

        elif gender and category_pk:
            qs = qs.filter(pk=category_pk)
            # print('\nCategoryLV_qs_category= ', '\n', qs, '\n')
            return qs

        elif gender and subcategory_pk:
            qs = SubCategory.objects.annotate(Count('item')).exclude(
                item__count=0).filter(pk=subcategory_pk)
            self.context_object_name = 'subcategory_shown'
            # print('\nCategoryLV_qs_sub_category= ', '\n', qs, '\n')
            return qs

    def get_validated_cats(self):
        categories_validated = []
        subcategories_validated = []
        items_validated = []

        brand_selected = self.brand_selected
        min_price = self.min_price
        if min_price == '' or min_price is None:
            min_price = 0
        max_price = self.max_price
        if max_price == '' or max_price is None:
            max_price = 999999

        for item in Item.objects.select_related('category', 'subcategory', 'tag').filter(category__gender=self.gender_number):
            if int(min_price) <= item.final_price < int(max_price):
                if brand_selected is None or brand_selected == 'бренд' or item.brand.name == brand_selected:
                    items_validated.append(item)
                    if item.category not in categories_validated:
                        categories_validated.append(item.category)
                    if item.subcategory not in subcategories_validated:
                        subcategories_validated.append(item.subcategory)

        return categories_validated, subcategories_validated, items_validated

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        context['brands'] = Brand.objects.all()

        cat_valid, subcat_valid, items_valid = self.get_validated_cats()
        context['filter_context'] = {
            'gender': self.gender,
            'gender_number': self.gender_number,
            'category_selected': self.category_selected,
            'brand_selected': self.brand_selected,
            'min_price': self.min_price,
            'max_price': self.max_price,
            'categories_validated': cat_valid,
            'subcategories_validated': subcat_valid,
            'items_validated': items_valid,
        }

        # print(context)
        return context

Answer 1

您应该使用prefetch_related预取“itemimage_set”，以便每次访问item.itemimage_set.all时都能得到缓存的结果

item = get_object_or_404(Item.objects.prefetch_related('itemimage_set'), pk=pk)

对于DetailView

class ItemDetailView(DetailView):
    model = Item
    queryset = Item.objects.prefetch_related('itemimage_set')