[Answered ]-Get least recently rented movies in Django

by

1👍

✅

With straight SQL, this would be much easier expressed like this:

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

The left join will produce a single row for each movie unrented since [TIME_ARG_HERE], but in those rows, the movieRentals.id column will be NULL.

Then, COUNT(movieRentals.id) will count all of the rentals where they exist, and return 0 if there was only the NULL value.

👤Ian Clelland

1👍

I must be missing something obvious. Why wouldn’t the following work:

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

Also, clauses can be chained (as shown in the code above), so there is no reason to constantly set a queryset variable to the intermediate querysets.

👤jeffknupp

Leave a comment