How to ORDER BY max no of same records in DJANGO

user_14081529 0
Viewed 67

I want to order by movies in the Movies model according to the max number of occurrences of a tuple in the MyMovies model.

models.py

class Movies(models.Model):
    mid = models.CharField(max_length=255, primary_key=True)
    title = models.CharField(max_length=255, null=True, blank=True)
    rating = models.CharField(max_length=5, null=True, blank=True)
    type = models.CharField(max_length=255, null=True, blank=True)
    genre = models.CharField(max_length=255, null=True, blank=True)
    rdate = models.CharField(max_length=255, null=True, blank=True)
    language = models.CharField(max_length=255, null=True, blank=True)
    cover = models.CharField(max_length=255, null=True, blank=True)
    description = models.TextField(null=True, blank=True)
    sequal = models.CharField(max_length=255, null=True, blank=True)
    trailer = models.CharField(max_length=255, null=True, blank=True)
    year = models.CharField(max_length=5, null=True, blank=True)
    objects = models.Manager()
    
    def __str__(self) -> str:
        return self.title

class MyMovies(models.Model):
    mid = models.ForeignKey(Movies, on_delete=CASCADE)
    uid = models.ForeignKey(User, on_delete=CASCADE, null=True, blank=True)
    watched = models.BooleanField()
    date = models.DateTimeField(auto_now_add=True)
    objects = models.Manager()

view.py

def showIndexPage(request):
    trending = list(MyMovies.objects.all().annotate(max_mid=Max(COUNT(mid))).order_by('-max_mid'))
    return render(request, 'index.html', {'trending': trending})

In the above code, MyMovies is my model with a foreign key mid referencing the Movie model.

So, if in MyMovies there are 2 movies with mid 1, 4 movies with mid 2 and 1 movie with mid 3

Then the result should be a list (trending) of attributes of Movies which is ordered by no. of occurrences of a particular movie id:

trending = [2, 1, 3]

django sql python
3 Answers

You can work with a .annotate() [Django-doc] and then .order_by(…) [Django-doc]:

from django.db.models import Count

Movies.objects.annotate(
    noccurence=Count('mymovies')
).order_by('-noccurence')

The Movies that arise from this QuerySet will have an extra attribute .noccurence that has the number of related MyMovies.

Since django-3.2 you can work with .alias(…) [Django-doc] to prevent calculating this both as column and in the ORDER BY clause:

from django.db.models import Count

Movies.objects.alias(
    noccurence=Count('mymovies')
).order_by('-noccurence')
user_675790

I would start the other way around and annotate the count to your Movie class:

from django.db.models import Count
trending = Movies.objects.all().annotate(mymovie_count=Count("mymovies")).order_by("-mymovie_count").values_list("id", flat=True)
user_59514010

Here is how the problem was resolved.

from django.db.models import Count

trending_m = MyMovies.objects.annotate(noccurence=Count('mid')).order_by('-noccurence').values_list('mid', flat='True')
trending = list(Movies.objects.filter(mid__in=trending_m))

If there is any other shorter way, please suggest. Thanks

user_140815290
Related