获取Django中最近租用的影片

Question

假设您有以下两个表：

CREATE movies (
    id int,
    name varchar(255),
    ...
    PRIMARY KEY (id)
);

CREATE movieRentals (
    id int,
    movie_id int,
    customer varchar(255),
    dateRented datetime,
    ...
    PRIMARY KEY (id)
    FOREIGN KEY (movie_id) REFERENCES movies(id)
);

对于直接使用SQL，我将此查询处理为：

^{pr2}$

（按id统计自给定日期以来所有电影租赁的数量）

显然，这些表的Django版本是简单的模型：

class Movies(models.Model):
    name = models.CharField(max_length=255, unique=True)

class MovieRentals(models.Model):
    customer = models.CharField(max_length=255)
    dateRented = models.DateTimeField()
    movie = models.ForeignKey(Movies)

但是，将其转换为等效查询似乎很困难：

timeArg = datetime.datetime.now() - datetime.timedelta(7,0)
queryset = models.MovieRentals.objects.all()
queryset = queryset.filter(dateRented__gte=timeArg)
queryset = queryset.annotate(rent_count=Count('movies'))

querysetTwo = models.Movies.objects.all()
querysetTwo = querysetTwo.filter(~Q(id__in=[val["movie_id"] for val in queryset.values("movie_id")]))
# Somehow need to set the 0 count. For now force it with Extra:
querysetTwo.extra(select={"rent_count": "SELECT 0 AS rent_count FROM app_movies LIMIT 1"})

# Now union these - for some reason this doesn't work:
# return querysetOne | querysetTwo
# so instead
set1List = [_getMinimalDict(model) for model in queryset]
# Where getMinimalDict just extracts the values I am interested in.
set2List = [_getMinimalDict(model) for model in querysetTwo]
return sorted(set1List + set2List, key=lambda x: x['rent_count'])

然而，虽然这种方法似乎有效，但它的速度非常慢。有没有更好的方法让我错过？在

Answer 1

我一定漏掉了一些明显的东西。为什么以下方法不起作用：

queryset = models.MovieRentals.filter(dateRented__gte=timeArg).values('movies').annotate(Count('movies')).aggregate(Min('movies__count'))

另外，子句可以被链接（如上面的代码所示），因此没有理由不断地为中间查询集设置queryset变量。在

Answer 2

如果使用纯SQL，这将更容易表达为：

SELECT movie.id, count(movieRentals.id) as rent_count
FROM movie
LEFT JOIN movieRentals ON (movieRentals.movie_id = movie.id AND dateRented > [TIME_ARG_HERE])
GROUP BY movie.id

left join将为自[TIME_ARG峎HERE]以来未重印的每部电影生成一行，但是在这些行中movieRentals.id列将为空。在

然后，COUNT(movieRentals.id)将计算所有存在的租金，如果只有空值，则返回0。在