Django高效获取关联广告数最多的品牌QuerySet

你在开发车辆租售信息发布网站时，需要查询关联已发布付费广告数量最高的汽车品牌做突出展示，当前实现通过Python遍历全量广告记录统计品牌发帖数，存在内存占用高、执行效率低的问题，希望改为通过数据库层聚合计算直接返回结果。

当前存在性能问题的实现 #

# Algorithm that is currently retrieving the name of the brand and the number of related posts it has.
def top_brand_ads():
     queryset = Advertisement.objects.filter(status__iexact="Published", owner__payment_made="True").order_by('-publish', 'name')

     result = {}
     for ad in queryset:
          # Try to update an existing key-value pair
          try:
               count = result[ad.brand.name.title()]
               result[ad.brand.name.title()] = count + 1

          except KeyError:
               # If the key doesn't exist then create it
               result[ad.brand.name.title()] = 1


     # Getting the brand with the highest number of posts from the result dictionary
     top_brand = max(result, key=lambda x: result[x])  # Returns for i.e. (Mercedes Benz)

     context = {
          top_brand: result[top_brand]  # Retrieving the value for the top_brand from the result dict.
     }
     
     print(context)  # {'Mercedes Benz': 7} -> Mercedes Benz has seven (7) related posts.
     return context

相关模型定义 #

# Brand
class Brand(models.Model):
     name = models.CharField(max_length=255, unique=True)
     image = models.ImageField(upload_to='brand_logos/', null=True, blank=True)
     slug = models.SlugField(max_length=250, unique=True)
     ...
     # Methods


# Owner
class Owner(models.Model):
     user = models.ForeignKey(User, on_delete=models.CASCADE)
     telephone = models.CharField(max_length=30, blank=True, null=True)
     alternate_telephone = models.CharField(max_length=30, blank=True, null=True)
     user_type = models.CharField(max_length=50, blank=True, null=True)
     payment_made = models.BooleanField(default=False)
     expiring = models.DateTimeField(default=timezone.now)
     ...
     # Methods


# Advertisement (Post)
class Advertisement(models.Model):
     STATUS_CHOICES = (
         ('Draft', 'Draft'),
         ('Published', 'Published'),
     )

     owner = models.ForeignKey(Owner, on_delete=models.CASCADE, blank=True, null=True)
     name = models.CharField(max_length=150, blank=True, null=True)
     brand = models.ForeignKey(Brand, on_delete=models.CASCADE, blank=True, null=True)
     publish = models.DateTimeField(default=timezone.now)
     status = models.CharField(max_length=10, choices=STATUS_CHOICES, default='Draft')
     ...
     # Other fields & methods

完全可以通过Django ORM的原生聚合能力实现，所有过滤、分组、计数、排序逻辑全部在数据库层执行，不需要加载全量广告记录到应用内存，数据量越大性能优势越明显。

实现方式1：仅返回品牌名和广告计数 #

和原逻辑的返回格式完全一致，兼容品牌名大小写不规范的存储场景：

from django.db.models import Count
from django.db.models.functions import Lower

def top_brand_ads():
    top_brand_agg = (
        Advertisement.objects
        .filter(status__iexact="Published", owner__payment_made=True)
        # 按品牌名分组，用Lower做大小写归一，避免同品牌因大小写差异被拆分为多个分组
        .values(brand_name=Lower('brand__name'))
        # 统计每个品牌下的广告数量
        .annotate(ad_count=Count('id'))
        # 按广告数倒序，数量最高的排在最前
        .order_by('-ad_count')
        # 仅取第一条结果，无符合条件数据时返回None
        .first()
    )

    context = {}
    if top_brand_agg:
        # 和原逻辑保持一致，对品牌名做title格式化
        format_brand_name = top_brand_agg['brand_name'].title()
        context[format_brand_name] = top_brand_agg['ad_count']
    
    print(context)
    return context

• 如果你的品牌名在数据库中存储时大小写完全统一，可以去掉Lower函数，直接写.values('brand__name')，执行效率会更高
• 去掉了原逻辑中无意义的.order_by('-publish', 'name')排序，这一步对统计品牌广告数没有任何作用，反而会增加数据库排序开销
• 修正了原逻辑中owner__payment_made="True"的字符串传参问题，BooleanField直接传布尔值True即可，避免隐式类型转换
• 兼容无符合条件广告的边界场景，不会出现空对象调用max()的异常

实现方式2：返回品牌模型QuerySet #

如果你需要同时拿到品牌的logo、slug等其他字段用于前端渲染，可以直接从Brand模型侧反向关联查询，返回的结果就是品牌模型实例：

from django.db.models import Count

def top_brand_ads():
    top_brand = (
        Brand.objects
        .filter(
            advertisement__status__iexact="Published",
            advertisement__owner__payment_made=True
        )
        .annotate(ad_count=Count('advertisement'))
        .order_by('-ad_count')
        .first()
    )

    context = {}
    if top_brand:
        # 直接通过模型实例取品牌字段，不需要额外查库
        context[top_brand.name.title()] = top_brand.ad_count
        # 可以直接取top_brand.image、top_brand.slug等其他字段使用
    return context

内容的提问来源于stack exchange，提问作者Damoiskii