CODEWITHSUNDEEP
pythondjangodjango-views
hubbaka ghoyati
hubbaka ghoyati

Reputation: 55

SQL to Queryset Django

i want to use this query in django, i have tried and read the django document but i still can't understand it

Sql to QuerySet Django

SELECT * FROM movie as m INNER JOIN similarity as s ON m.`movie_id` = s.first_movie WHERE `movie_id` = 1 ORDER BY `s`.`similarity_score` DESC

This is My model

from django.db import models

# Create your models here.
class Movie(models.Model):
    movie_id = models.IntegerField(primary_key=True)
    title = models.CharField(max_length=200)
    genres = models.CharField(max_length=200)
    num_ratings = models.IntegerField(null=True)
    rating_median = models.FloatField(null=True)
    rating_mean = models.FloatField(null=True)
    comparable = models.BooleanField(default=True)
    liked = models.NullBooleanField(null=True, blank=True)

    def __str__(self):
        return self.title


class Similarity(models.Model):
    first_movie = models.ForeignKey(Movie, related_name='first_movie', on_delete = 'CASCADE')
    second_movie = models.ForeignKey(Movie, related_name='second_movie', on_delete = 'CASCADE')
    similarity_score = models.FloatField()


class OnlineLink(models.Model):
    movie = models.ForeignKey(Movie, on_delete = 'CASCADE')
    imdb_id = models.CharField(max_length=50)
    youtube_id = models.CharField(max_length=50, null=True)
    tmdb_id = models.CharField(max_length=50, null=True)

Upvotes: 0

Views: 133

Answers (2)

Dean Elliott
Dean Elliott

Reputation: 1323

You can use prefetch_related for forward foreign keys.

movies = Movie.objects.all().prefetch_related(
    'first_movie'
).filter(
    first_movie__id=1
).order_by(
    "-similarity_score"
)

This will not create an SQL join but will do the join in Python. If you must have a join, you need to start from the other table like so:

similarities = Similarity.objects.filter(
    first_movie__id=1
).order_by(
    "-similarity_score"
).select_related(
    'first_movie'
)

Using select_related does an inner join, so you don't hit the table every time you want to access a field from the Movie table.

See Django docs for prefetch_related and select_related.

See this StackOverflow post for prefetch_related and select_related.

Upvotes: 1

Trafalino
Trafalino

Reputation: 145

A solution could be the following:

s = Similarity.objects.filter(first_movie__id=1).order_by("-similarity_score")
# and access to first_movie
for movie in s:
    movie.first_movie

Upvotes: 0

Related Questions