What's the most efficient way to retrieve django queryset with the hightest number of posts for a related name?

Question

I'm currently working on a website where advertisements will be posted to display vehicles for sale and rent. I would like to retrieve a queryset that highlights only one car brand (i.e. Audi) which has the highest number of posts for the respective model. Example:

Displaying the Audi brand because it has the highest number of related posts.

My question is, what's the most efficient way of doing this? I've done some work here but I'm pretty sure this is not the most efficient way. What I have is the following:

# 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

Is there a way I could return a queryset instead without doing what I did here or could this be way more efficient?

If the related models are needed, please see below:

models.py

# 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

Any help would be greatly appreciated.

Answer 1

Since you need brands, let's query on Brand model:

Brand.objects.filter(advertisement__status__iexact="Published").\
              filter(advertisement__owner__payment_made=True).\
              annotate(published_ads=Count('advertisement__id')).\
              order_by('-published_ads')

However, even in your proposed solution, you can improve a little bit:

1. Remove the order_by method from your queryset. It doesn't affect the final result but adds some overhead, especially if your Advertisement model is not indexed on those fields.

2. Every time you call ad.brand you are hitting the database. This is called the N+1 problem. You are in a loop of n, you make n extra db access. You can use select_related to avoid such problems. In your case: Advertisement.objects.select_related('brand')...