gpt4 book ai didi

python - 在 Django 中获取最近最少租借的电影

转载 作者:行者123 更新时间:2023-12-01 06:04:27 26 4
gpt4 key购买 nike

假设您有以下两个表:

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,我会按如下方式处理此查询:

(
SELECT movie_id, count(movie_id) AS rent_count
FROM movieRentals
WHERE dateRented > [TIME_ARG_HERE]
GROUP BY movie_id
)
UNION
(
SELECT id AS movie_id, 0 AS rent_count
FROM movie
WHERE movie_id NOT IN
(
SELECT movie_id
FROM movieRentals
WHERE dateRented > [TIME_ARG_HERE]
GROUP BY movie_id
)
)

(按 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'])

然而,虽然这种方法似乎有效,但速度却非常慢。我失踪了还有更好的方法吗?

最佳答案

使用直接 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

左连接将为自 [TIME_ARG_HERE] 以来未租借的每部电影生成一行,但在这些行中,movieRentals.id 列将为 NULL。

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

关于python - 在 Django 中获取最近最少租借的电影,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8854328/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com