Written by Super User.

9- Queries in Many To Many Relationship

Before moving on we can test our queries on Terminal. Make sure virtual environment is activated. First start shell on terminal with the command below:

python manage.py shell

 

Import your models

from moviesapp.models import MyCustomUser, Movie, UserComment, Watched, Actor

 

create a movie and display all movies

movieobj = Movie.objects.create(title="Forrest Gump")
Movie.objects.all()

#It will return the queryset
#<QuerySet [<Movie: Brave Heart>, <Movie: Fight Club>, <Movie: Gladiator>, <Movie: Forrest Gump>]>

 

 

Add a new actor to the database

actor = Actor.objects.create(actorname="Tom Hanks")
Actor.objects.all()

#It will return the queryset
#<QuerySet [<Actor: Brad Pitt>, <Actor: Edward Norton>, <Actor: Sophie Marceau>, <Actor: Tom Hanks>]>

 

Associate the actor with the movie we just created

actor.movies.add(movieobj)
actor.movies.all()

#<QuerySet [<Movie: Forrest Gump>]>

 

Create more movies and associate the actor with those movies

movieobj2 = Movie.objects.create(title="Saving Private Ryan")
movieobj3 = Movie.objects.create(title="The Green Mile")
actor.movies.add(movieobj2, movieobj3)
actor.movies.all()

#<QuerySet [<Movie: Forrest Gump>, <Movie: Saving Private Ryan>, <Movie: The Green Mile>]>

 

Add a movie for that actor in single step

actor.movies.create(title="Cast Away")
actor.movies.all()

#<QuerySet [<Movie: Forrest Gump>, <Movie: Saving Private Ryan>, <Movie: The Green Mile>, <Movie: Cast Away>]>


Movie.objects.all()

#<QuerySet [<Movie: Brave Heart>, <Movie: Fight Club>, <Movie: Gladiator>, <Movie: Saving Private Ryan>, <Movie: Forrest Gump>, <Movie: Saving Private Ryan>, <Movie: The Green Mile>, <Movie: Cast Away>]>

 

We can run queries across the relationships like this (List the actors who has a movie title that starts with F  (Forrest Gump, Fight Club))

Actor.objects.filter(movies__title__startswith="F")

#<QuerySet [<Actor: Brad Pitt>, <Actor: Edward Norton>, <Actor: Tom Hanks>]>

 

You can add related_name field to your ManyToMany field and after running migration commands, you can run reverse queries.

class Actor(models.Model):
    actorname = models.CharField(max_length=25, blank=True, null=True)
    movies = models.ManyToManyField(Movie, related_name="moviemodel")
    def __str__(self):
        return self.actorname

 

 

Movie.objects.filter(moviemodel__actorname__startswith="B")

#<QuerySet [<Movie: Fight Club>]>

 

Delete an Actor record:

Actor.objects.filter(movies__title__contains="Cast")
#<QuerySet [<Actor: Tom Hanks>]>

objToDelete = Actor.objects.filter(movies__title__contains="Cast")
objToDelete.delete()


Actor.objects.filter(movies__title__contains="Cast")
#<QuerySet []>

 

Delete Movies which belong to a specific Actor

Movie.objects.filter(moviemodel__actorname__contains="Tom")

#<QuerySet [<Movie: The Green Mile>, <Movie: Saving Private Ryan>]>

deletemov = Movie.objects.filter(moviemodel__actorname__contains="Tom")
deletemov.delete()


Movie.objects.filter(moviemodel__actorname__contains="Tom")
<QuerySet []>

 

Query Examples Between Other Through Tables:

Note that when we run queries the related_names that we defined in models.py (Comment_User, Comment_Movie, Watched_User, Watched_Movie) are used for the look up

This query returns movies which are watched by selim
Movie.objects.filter(Watched_Movie__user__username="selim")
#<QuerySet [<Movie: Fight Club>]>



#This query returns movies which has text "Great" in their comments
Movie.objects.filter(Comment_Movie__comment__contains="Great")
#<QuerySet [<Movie: Brave Heart>]>

#This query returns movies for which user selim commented
Movie.objects.filter(Comment_Movie__user__username = "selim")
#<QuerySet [<Movie: Brave Heart>]>


#This query returns the movies which are related with user "Betül"
Movie.objects.filter(users__username="Betül")
#<QuerySet [<Movie: Gladiator>]>


#This query returns the users who has watched Brave Heart
MyCustomUser.objects.filter(Watched_User__movie__title="Brave Heart")
#<QuerySet [<MyCustomUser: Betül>, <MyCustomUser: selim>]>


#This query returns the users who has commented on Gladiator movie 
MyCustomUser.objects.filter(Comment_User__movie__title="Gladiator")
#<QuerySet [<MyCustomUser: Betül>]>


#This query returns all comments of user selim
UserComment.objects.filter(user__username="selim")
#<QuerySet [<UserComment: Great movie. This is my second time watching>]>

#This query returns all commentsfor a specific movie
UserComment.objects.filter(movie__title="Brave Heart")
#<QuerySet [<UserComment: Great movie. This is my second time watching>, <UserComment: This is old but gold>]>