optimize this query that selects rows before and after a pk
I'm trying to select 2 rows before and after a given primary key. This is my solution so far开发者_Go百科, but seems inefficient. Is there a way to optimize this?
images = Media.objects.filter(owner=user_object) #select objects by a user
id_list = list(images.values_list('id', flat=True)) #get the primary keys for the objects
idx = id_list.index(image.id) #get the index of the image
#TODO check for index error
next_ids = id_list[idx+1:idx+3] #get ids of next 2 objects
prev_ids = id_list[idx-3:idx-1] #get ids of previous 2 objects
#using the IDs, get the objects
next_objs = Media.objects.filter(Q(pk=next_ids[0]) | Q(pk=next_ids[1]))
prev_objs = Media.objects.filter(Q(pk=prev_ids[0]) | Q(pk=prev_ids[1]))
This works, but I'm looking for something more efficient, maybe using more advanced techniques of django's querysets
Get the rows with an ID less/greater than yours, and limit it to 2 rows.
next_objs = Media.objects.filter(id__gt=image.id).order_by('id')[:2]
prev_objs = Media.objects.filter(id__lt=image.id).order_by('-id')[:2]
精彩评论