SQL to Queryset Django

Question

我想在django中使用这个查询，我试过阅读django文档，但我仍然无法理解

Sql到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

这是我的型号

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)

Answer 1

您可以将prefetch_related用于前向外键。

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

这不会创建SQL联接，而是在Python中执行联接。如果你必须有一个联接，你需要从另一个表开始，如下所示：

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

使用select_related执行内部联接，因此不会每次想要访问Movie表中的字段时都碰到表。

请参阅Django文档中的prefetch_related和select_related。

请参阅这篇StackOverflow文章，了解prefetch_related和select_related。

Answer 2

解决方案可能如下：

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