Django: how to filter by most selling item

i have a course models and user are allowed to purchase this courses, now i want to filter the courses by the most selling course, i have tried using django agreegate but it seems not to be giving me what i want or maybe i am not doing it the right way.

i have models.py that is storing the courses

class Course(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    course_title = models.CharField(max_length=100, null=True, blank=True)
    slug = models.SlugField(unique=True)
    course_creator = models.ForeignKey(User, on_delete=models.CASACDE)

i also have this model that stores the courses that are purchased and enrolled in

class UserCourse(models.Model):
    user = models.ForeignKey(User , null = False , on_delete=models.CASCADE)
    course = models.ForeignKey(Course , null = False , on_delete=models.CASCADE)
    date = models.DateTimeField(auto_now_add=True)

i dont know how to write the view to filter the courses by the most selling from the image below you can see that learn angular updated is the most selling course for the creator destiny . so how do i filter this for each user, thier most selling course?

To make sure I understand what you’re asking -

You’re looking to create a view,
that will display a list,
where each element of the list is the Course Creator and their top-selling Course.

Is that correct?

If so, where is the Course Creator in your models?

Also, how do you want it handled if a Course Creator has multiple Courses all selling the same amount?

Does your output also need to include the number of sales for that top-selling course?

1 Like

Yes that is correct, a view that would lists out all the top selling courses for each creators.

Also the course_creator field is in the Course Models

Yes, My output need to include the number of sales for that top-selling course.

Thanks, that helps a lot.

Still need clarification on this:

Also, do you want something different to be done if a Course Creator hasn’t sold any? (All their courses have 0 sales.)

1 Like

I would still want to list all the courses even tho there are selling the same numbers

Ok, so it’s not necessarily one line per creator. It could be many rows per creator, where each row is their top-selling course, with ties.

1 Like

Yes, that’s exactly what it is

Ok, my first thought on this then is to use a Subquery. I’d write a Subquery to find the Max of the number of sales of each course for a creator and return that value to the outer query. The outer query would then filter the queryset for the courses where the number of sales equals that max value.

You may want those values grouped by creator. See the discussion here for some ideas - sum a grouped data (I’m not sure about this - I’m not sure I know right off what you’re going to get as a result of the subquery-style query.)

One other thought I had that may be helpful -

You want to look at this as “Get the list of courses that are the top selling for each creator” and not as “Get each creator and their top-selling course”. This change in perspective is important because you may end up getting multiple courses for a creator.