How to optimize a Django query?

Question

Django

Good afternoon! There are the following models:

class Product(CreationModificationDateMixin, MetaTagsMixin, ImageThumbnailMixin):
  ...
  brand = models.ForeignKey(
    Brand,
    null=True,
    blank=True,
    related_name='products',
    verbose_name='Производитель',
    on_delete=models.SET_NULL
  )

  material = models.ForeignKey(
    Material,
    verbose_name='Материал',
    blank=True,
    null=True,
    related_name='products',
    on_delete=models.SET_NULL
  )

class Material(models.Model):
  name = models.CharField(
    'Название',
    max_length=100
  )

class Brand(CreationModificationDateMixin, MetaTagsMixin):
  country = models.ForeignKey(
    Country,
    blank=True,
    null=True,
    verbose_name='Страна производителя',
    related_name='brands',
    on_delete=models.SET_NULL
  )

  name = models.CharField(
    'Название бренда',
    max_length=100,
  )

class Country(models.Model):
  name = models.CharField(
    'Название',
    max_length=200
  )

Assignment: to bring out the countries, then in the countries to bring out the brands grouped by materials. Example below:

I did what I did, but I went through the profiler and, quite expectedly, received a bunch of the same type of requests. Did it like this:

countries = Country.objects.all()
    materials = Material.objects.all()
    country_materials = []

    for country in countries:
        dc = {'country': country, 'materials': []}

        for material in materials:
            brands = Brand.objects.filter(
                products__material=material, country=country).distinct()

            if brands.exists():
                dc['materials'].append({'material': material, 'brands': brands})

        if dc['materials']:
            country_materials.append(dc)

    return {
        'menu': menu,
        'country_materials': country_materials
    }

The problem is that the menu is displayed on all pages.
I tried to reach out through prefetch_related, but it didn’t work out: I tried ```Brand.objects.prefetch_related('products__material')``` but I get the goods, but as I understand it, I need materials. How can you optimize or change the approach to building a query so that a bunch of queries do not go to the database?

Answer 1

Just remove the loops and make one request:

brands = Brand.objects.filter(
    products__material__in=materials, 
    country__in=countries
).distinct()

Answer 2

after a couple of months of such torment, I rewrote all queries to raw sql without using the jang orm, where you need to display a lot of related data, it generates an infernal amount of selects (and even with the no_cache option) and slows down a lot