Can't find Django Shell command to purge records from one table, where matching records DON&#03-CodePudding

Django / ORM

I have two tables, and I'm trying to purge / find all records in one table, where no matching records exist in the matching table.

I'm using the Django Shell to test, but can't figure the command to say (in plain English)...

Delete/Show all records in my movie table where there are no ratings for it in the ratings table

The Movie table (obviously) has an id field, and the Rating table has a matching FK movie_id field)

The tables are:

class Movie(models.Model):
    imdbID = models.TextField(unique=True)
    title = models.TextField()
    year = models.TextField()

class Rating(models.Model):
    movie = models.ForeignKey(Movie,on_delete=CASCADE)
    user = models.ForeignKey(User,on_delete=CASCADE)  
    rating = models.IntegerField()

A few of the Django commands I have ran are (to try to get close):

>>> Movie.objects.all()                      ;gives all movies
>>> Rating.objects.all()                     ;gives all ratings
>>> Rating.objects.filter(movie__year=2018)  ;works

But when i try to dig deeper: (trying to ask for movies where movie_id equals rating movie_id), i get stuck...

>>> Movie.objects.filter(movie_id=rating__movie)

Traceback (most recent call last): File "", line 1, in NameError: name 'rating__movie' is not defined

>>> Movie.objects.filter(rating__movie=movie_id)

Traceback (most recent call last): File "", line 1, in NameError: name 'movie_id' is not defined

I really want the NOT "WHERE' condition, but I can't even get the TRUE condition

CodePudding user response：

You are trying to get data of the two models, but to do that you have to use the related_name of a foreign key 'relationship 'backward'. You can set in your models definition.

class Rating(models.Model):
    movie = models.ForeignKey(Movie,on_delete=CASCADE, related_name='rating_movie')
    user = models.ForeignKey(User,on_delete=CASCADE, related_name='rating_user')

If you didn't do, the name of each one will be namemodel_set.

Now, for example, to get data of first movie rated.

movie_rated_1 = Rating.objects.get(id=1)
movie = movie_rated_1.rating_movie.all()

UPDATE

I only wrote examples about how to use the 'related names'. Sorry.

Anyway... To delete the movies that doesn't have a review. I think, you can use difference() function and select_related().

movies_reviewed = Rating.objects.select_related('movie').all()
movies_to_delete = Movie.objects.all().difference(movies_reviewed)
print(movies_reviewed) # To verify
movies_to_delete.delete()

Even it is possible write in one line.

Movie.objects.all().difference(Rating.objects.select_related('movie').all()).delete()

CodePudding user response：

Got this from Ken at djangoproject.com, works great... thanks Ken ...posted here for benefit of future searchers.

Movie.objects.filter(rating__isnull=True)  or
Movie.objects.filter(rating__isnull=True).delete()

/fj