How to improve this query gange?

need to get previous and next object in the query, sorted by a text field ?
id_list = list(Product.objects.values_list('id', flat=True).order_by('title'))
next_id = id_list[id_list.index(cur_obj.id)+1]
July 9th 19 at 11:15
2 answers
July 9th 19 at 11:17
As a variant like this (take for example their model, simply changing the names under yours):
In [1]: Product.objects.values_list('id', flat=True).order_by('title')
Out[1]: [6, 7, 2, 15, 5, 4, 12, 14, 16, 1, 13, 10, 8, 11, 9, 3]

In [2]: my_pk = 12

In [3]: Product.objects.raw('SELECT * FROM product WHERE title<(SELECT title FROM product WHERE id=%s) ORDER BY title DESC LIMIT 1',[my_pk])[0].pk
Out[3]: 4

In [4]: Product.objects.raw('SELECT * FROM product WHERE title>(SELECT title FROM product WHERE id=%s) ORDER BY title ASC LIMIT 1',[my_pk])[0].pk
Out[4]: 14
On goods with the same name spin, in addition, you can use the same ORM tools: Product.objects.filter(title__gt=cur_obj.title).order_by('title').first() and Product.objects.filter(title__lt=cur_obj.title).order_by('-title').first() - magdalena_Bosco70 commented on July 9th 19 at 11:20
: on the same - spin - Nathan_Gaylord commented on July 9th 19 at 11:23
July 9th 19 at 11:19
id_list = list(Product.objects.values_list('id', flat=True).order_by('title'))
current_pos = id_list.index(cur_obj.id)
next_and_prev = list(Product.objects.all().order_by('title')[current_pos-1:current_pos+2])

No special optimization, from 3 queries to 2.
Full code if what I have is this:
sorted_products = list(
Products.objects.order_by('title').values_list('id', flat=True)
)
current_product_position = sorted_products.index(product_id)
slice_start = (
current_product_position - 1
if current_product_position > 0
else 0
)
slice_end = (
current_product_position + 2
if current_product_position != len(sorted_products)
else len(sorted_products)
)
next_and_prev = list(
Products.objects.order_by('title')[slice_start:slice_end]
)

next_product = None
previous_product = None

if next_and_prev:
if current_product_position != 0:
previous_product = next_and_prev[0]
if current_product_position != len(sorted_products):
next_product = next_and_prev[-1] - magdalena_Bosco70 commented on July 9th 19 at 11:22
I mean the optimization that if the products will > 1000/3000/10000, then the design will grow - Nathan_Gaylord commented on July 9th 19 at 11:25
: here's a bike, not yet found where it does not function properly:
objects_list = Product.objects.order_by('title', 'id)

# next product
filter_param = dict()
if product.pk == objects_list.filter(title=product.title).values_list('pk', flat=True).order_by('-id').first():
filter_param.update({'title__gt': product.title})
else:
filter_param.update({'title__gte': product.title,'pk__gt': product.pk,})
next_product = Product.objects.filter(**filter_param).order_by('title', 'id).first()

# previous product
filter_param = dict()
if product.pk == objects_list.filter(title=product.title).values_list('pk', flat=True).first():
filter_param.update({'title__lt': product.title})
else:
filter_param.update({'title__lte': product.title,'pk__lt': product.pk,})
previous_product = Product.objects.filter(**filter_param).order_by('-title', '-id).first() - magdalena_Bosco70 commented on July 9th 19 at 11:28

Find more questions by tags PythonDjango