imagine:
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)
);
我直截了当地认为:
(
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
)
)
(自指定日期起,按补贴计算所有电影租金)
显然,这些表格的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 ])
然而,尽管这种方法似乎可行,但令人难以置信的是缓慢。 是否有更好的办法失踪?