I receive on SQL request for each element images. If you remove {% if img.categories == category%} in the template, then the number of requests is normal, but how then to figure them out is not entirely clear. Tried to use {% regroup by as%} in the template, but then the same large number of queries ... Help optimize this outrage ... Thank you.

In models.py

class Catalog(models.Model): name = models.CharField(max_length=100) def __unicode__(self): return self.name class Categories(models.Model): name = models.CharField(max_length=200) catalog = models.ForeignKey(Catalog) def __unicode__(self): return self.name class Images(models.Model): name = models.CharField(max_length=200) img = ResizedImageField(upload_to='catalog') categories = models.ForeignKey(Categories)

In views.py

 def catalogManufacturer(request, url): args = {} args['catalog'] = Catalog.objects.get(url=url) args['categories'] = Categories.objects.filter(catalog=args['catalog'].id) args['images'] = Images.objects.filter(categories=args['categories']) return render_to_response('catalog-manufacturer.html', args)

Well, the template.

 {% for category in categories %} <h2>{{category.name}}</h2> <ul> {% for img in images %} {% if img.categories == category %} <li> <a href="{% cropped_thumbnail img "cropping" scale=1 %}"> <img src="{% cropped_thumbnail img "cropping" scale=0.6 %}" alt="{{img.name}}"> </a> {{img.name}} </li> {% endif %} {% endfor %} </ul> {% endfor %}
  • And write, where do you get cropped_thumbnail (not the fact that it affects, but just in case) - andreymal
  • And note: the models are better named Category and Image - andreymal
  • Why not request all the images for the category through the link models? Category.image_set.all or category.images.all (I don’t remember exactly). If there are few categories, it should reduce the number of requests. - Chikiro

2 answers 2

Objects of the QuerySet type do not access the database during creation — these objects are "lazy" ( https://docs.djangoproject.com/ja/1.9/topics/db/queries/#querysets-are-lazy ). This means that calls to the database will be created when it is really necessary. For example, during an iteration over elements from the QuerySet. You can wrap the QuerySet in the list, then all the objects will be obtained from the database immediately ( https://docs.djangoproject.com/en/1.9/ref/models/querysets/#when-querysets-are-evaluated ):

 args['images'] = list(Images.objects.filter(categories=args['categories']))

There is a drawback to this approach - if there are so many objects in the database that they don’t all fit into memory at once, a MemoryError exception will be thrown. In this case, you can write a simple image generator that will drag objects not one by one, but, for example, 100 each in one request:

 def queryset_gen(query): total_items = query.count() batch_size = 100 fetched_items = 0 batch_seeker = 0 batch = list(query.all()[:batch_size]) current_offset = batch_size while fetched_items < total_items: next_item = batch[batch_seeker] yield next_item batch_seeker += 1 if batch_seeker == batch_size or batch_seeker == len(batch): batch_seeker = 0 fetched_items += batch_size # [OFFSET:LIMIT] batch = list(query.all()[current_offset:batch_size]) current_offset += batch_size
  • Unfortunately, wrapping in the list of results did not give ... args ['images'] = list (Images.objects.filter (categories = args ['categories'])) Maybe I don’t understand something else? I will be glad to clarify ... - roffl
  • Even wrapped QuerySet still generates a lot of single queries instead of one? Try list(Images.objects.filter(categories=args['categories']).all()) - the all () method has been added, but judging by the documentation it shouldn’t help - m9_psy
  • And so and so many requests .... I think that this is somehow connected with the fact that in the template several times the images object goes through cycles, but I am not sure. - roffl
  • If it is known that there are a lot of requests, can we know what is specifically for the requests? You have a foreign key in the model and it’s not a fact that the linked table is automatically linked for the images. In SQLAlchemy, for example, foreign keys by default are also lazy. To have all linked tables load automatically, try the docs.djangoproject.com/en/dev/ref/models/querysets/ ... method. But in this case it is worth very closely monitor the memory. - m9_psy
  • I apologize for not giving it before. These types of queries are duplicated ..... SELECT "catalog-categories". "Id", "catalog-categories". "Name", "catalog-categories". "Catalog_id" FROM "catalog-categories" WHERE "catalog -categories "." id "= 18 I will add that" catalog-categories "is a Categories category model. - roffl

The code strange at you places of course. This construction of Catalog.objects.get(url=url) does not fall?

Let's rewrite your models a bit and try to understand what is going on in your code

We declare a category (you call it a Catalog), everything seems fine

 class Category(models.Model): name = models.CharField(max_length=100) def __unicode__(self): return self.name

Announce the model of the image. She will have a link to the category in which she is presented, the name and the image itself.

 class Image(models.Model): name = models.CharField(max_length=200) category = models.ForeignKey(Category) image = models.ImageField(upload_to='catalog') def __unicode__(self): return self.name

I don’t understand what kind of second model you have for Images, but most likely these are your attempts to use some kind of third-party component (most likely django-resized - you should specify the question in general) - judging by the code, it does not generate any additional requests. Now we will write view

 from django.shortcuts import get_object_or_404 def CategoryManufacturer(request, id): category = get_object_or_404(Category, id=id) image_list = Image.objects.filter(category=category).select_related('category') args = { 'category': category, 'images': image_list } return render_to_response('catalog-manufacturer.html', args)

What's going on here?

We take the id from the link (you should have a url of the form url(r'^(?P<id>[0-9]+)/$', ...) and get the category. If the category is not found, get_object_or_404 will return 404 error. If there is a category, then we get all the images that are in this category. Here it is also worth mentioning select_related, which loads a category for each image without additional request. Well, we give back

What kind of cropped_thumbnail in your template I do not know, but I suspect that you just mixed a bunch of components and do not know which one to choose. Clean the code and everything will fall into place.