Next Row of a Queryset...

Using Django Using the ORM
1

Hi again folks,

at the moment I am struggeling with the question how to get next/previous row of a given queryset (while implementing NEXT/PREVIOUS buttons in Template)

If I work with this:

employee_qs = Employees.objects.filter(id=employee).order_by('last_name')

I could easily do the task by

employee = int(employee) + 1

or alternatively

employee = int(employee) - 1

(of course there would be need of error handling, if there is no higher or lower number but its only for show)

BUT I would like to switch to next/previous dataset in the SORTED queryset. So i don’t have to deal with the id, but with the last_name entry…

2

Hey @doc1977! I suggest you have a look at this answer which suggests using Subquery for that.

3

Another approach is to select page+1 objects from the database then render the next button if there was Page+1 returned versus a lesser amount.

4

Hi there and thx for the input. My problem is, that I DON’t want to deal with the id’s but with the alphabetically sorted entry for last_name

So if my current entry wohuld have id=5, the next entry in the sorted list of last_names could have id=1 or id= 355…

Main problem to me: How can i discover the next/previous row in the sorted list of last_names ?

5

The concepts charettes and I proposed are the same. You can use a Subquery to grab the previous and next last name. Give those docs a look and play around with the ORM in a shell / REPL.

The other approach is still valid too. Increase your page size by one so you select one more to determine if there’s a next page.

Side note about sorting on last name. It’s unlikely to be unique so you need to add other sorting keys so that you end up with an fixed order. So maybe the second sort key is an id type field?

6

Another aside, I think DRFs CursorPagination docs could be helpful to you Pagination - Django REST framework

7

So maybe the second sort key is an id type field?

I was already thinking about implementing kind of alphabetical_idfield in model an have this populated with ascending integers in the alphabetically sorted list. Afterwards one could easily use this integer values to navigate through NEXT/PREVIOUS by simply incrementing or decrementing this value. This would make it necessary to rewrite this index with every create or delete process within the databse entries, however. And here I am thinking of perfromance in the end, wether this is not a BAD idea in the end…

8

I’m not sure I follow you. However, integers sorted as strings is rarely a good idea. Humans never expect that.

9

After all my core problem is still:

Take for example:

Model.objects.all().order_by('last_name')

no use

Model.objects.filter(id=request.POST('id'))

to show data in template.

How can i discover the previous/next dataset in a .oder_by('last_name') queryset starting from the filtered query based on id?

10

Ok, tried it now this way and it seems to work:

def prev_next(employee, request):

    unique_id = Employees.objects.get(id=employee).employee_number
    id_list = list(Employees.objects.all().order_by('last_name').values_list('employee_number', flat=True))
    position = id_list.index(unique_id)
    if 'next' in request.POST and position < id_list.index(max(id_list) - 1):
        position = position + 1
    elif 'previous' in request.POST and 0 < position:
        position = position - 1

    employee = Employees.objects.get(employee_number=id_list[position]).id

    return employee