CODEWITHSUNDEEP
djangogroup-bycountmany-to-manysql-order-by
micleb
micleb

Reputation: 39

Django : Count, Group By and Order By with many to many fied

I have two models :

class Actors(models.Model):
    name = models.CharField(max_length=128)
    ...


class Movies(models.Model)
    title = models.CharField(max_length=128)
    casting = models.ManyToManyField("actors.Actors", related_name="casting")
    ...

I am looking for ordering actors by using the number of movies they played in. Django autogenerated a table with actors_id and movies_id, named movies_movies_casting :

+-----+-----------+-----------+
| id  | movies_id | actors_id |
+-----+-----------+-----------+
|   1 |         1 |         1 |
|   2 |         1 |         2 |
|   3 |         2 |         1 |
|   4 |         3 |         1 |
...

Here actor #1 played in movies #1, #2 and #3. And actor #2 played only in movie #1.

Here is the request used in MySQL to get what I want :

SELECT actors_id, COUNT(movies_id) as count FROM movies_movies_casting GROUP BY actors_id ORDER BY count;

The result is :

+-----------+-------+
| actors_id | count |
+-----------+-------+
|        15 |    29 | // actor #15 played in 29 movies
|        12 |    21 |
|        24 |    17 |
|         3 |    16 |
|        20 |    15 |
|        21 |    14 |
...

What is the request used in Django to get this result ?

Upvotes: 3

Views: 560

Answers (1)

JPG
JPG

Reputation: 88459

Try this,

from django.db.models import Count

Movies.objects.values('casting'
                      ).annotate(
    count=Count('id')).order_by('-count')

Upvotes: 1

Related Questions