python - Get least recently rented movies in Django -

python - Get least recently rented movies in Django -

so imagine have next 2 tables:

create movies ( id int, name varchar(255), ... primary key (id) ); create movierentals ( id int, movie_id int, client varchar(255), daterented datetime, ... primary key (id) foreign key (movie_id) references movies(id) );

with sql directly, i'd approach query as:

( select movie_id, count(movie_id) rent_count movierentals daterented > [time_arg_here] grouping movie_id ) union ( select id movie_id, 0 rent_count film movie_id not in ( select movie_id movierentals daterented > [time_arg_here] grouping movie_id ) )

(get count of film rentals, id, since given date)

obviously django version of these tables simple models:

class movies(models.model): name = models.charfield(max_length=255, unique=true) class movierentals(models.model): client = models.charfield(max_length=255) daterented = models.datetimefield() film = models.foreignkey(movies)

however, translating equivalent query appears difficult:

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"] val in queryset.values("movie_id")])) # somehow need set 0 count. forcefulness extra: querysettwo.extra(select={"rent_count": "select 0 rent_count app_movies limit 1"}) # union these - reason doesn't work: # homecoming querysetone | querysettwo # instead set1list = [_getminimaldict(model) model in queryset] # getminimaldict extracts values interested in. set2list = [_getminimaldict(model) model in querysettwo] homecoming sorted(set1list + set2list, key=lambda x: x['rent_count'])

however, while method seems work, incredibly slow. there improve way missing?

with straight sql, much easier expressed this:

select movie.id, count(movierentals.id) rent_count film left bring together movierentals on (movierentals.movie_id = movie.id , daterented > [time_arg_here]) grouping movie.id

the left bring together produce single row each film unrented since [time_arg_here], in rows, movierentals.id column null.

then, count(movierentals.id) count of rentals exist, , homecoming 0 if there null value.

python sql django