Order queryset by alternating value

Since you use Postgres you can use its Window Functions which perform a calculation across a set of table rows that are somehow related to the current row. Another good information relies in the fact that you use Django2.x which supports Window Functions(Django docs) which allows adding an OVER clause to Querysets.

Your use-case can be resolved with Single ORM query like:

from django.db.models.expressions import Windowfrom django.db.models.functions import RowNumberfrom django.db.models import Fresults = Entry.objects.annotate(row_number=Window(    expression=RowNumber(),    partition_by=[F('client')],    order_by=F('created').desc())).order_by('row_number', 'client')for result in results:    print('Id: {} - client: {} - row_number {}'.format(result.id, result.client, result.row_number))

Output:

Id: 12 - client: facebook - row_number 1Id: 13 - client: google - row_number 1Id: 11 - client: facebook - row_number 2Id: 8 - client: google - row_number 2Id: 10 - client: facebook - row_number 3Id: 5 - client: google - row_number 3Id: 9 - client: facebook - row_number 4Id: 3 - client: google - row_number 4Id: 7 - client: facebook - row_number 5Id: 2 - client: google - row_number 5Id: 6 - client: facebook - row_number 6Id: 1 - client: google - row_number 6Id: 4 - client: facebook - row_number 7

The raw SQL looks like:

SELECT "orm_entry"."id","orm_entry"."name","orm_entry"."client","orm_entry"."created",ROW_NUMBER() OVER (PARTITION BY "orm_entry"."client" ORDER BY "orm_entry"."created" DESC) AS "row_number" FROM "orm_entry" ORDER BY "row_number" ASC, "orm_entry"."client" ASC

Window functions are declared just as an aggregate function followed by an OVER clause, which indicates exactly how rows are being grouped. The group of rows onto which the window function is applied is called "partition".

You can notice that we grouped the rows by 'client' field and you can conclude that in our example we will have the two partitions. First partition will contain all the 'facebook' entries and second partition will contain all the 'google' entries. In its basic form, a partition is no different than a normal aggregate function group: simply a set of rows considered "equal" by some criteria, and the function will be applied over all these rows to return a single result.

In your example we can use the row_number window function which simply returns the index of the current row within its partition starting from 1. That helped me to establish the alternating output in order_by('row_number', 'client').

Additional information:

If you want to achieve an order like this:

'facebook','facebook', 'google','google','facebook','facebook','google','google'

or

'facebook','facebook','facebook','google','google','google','facebook', 'facebook','facebook'

You will need to do one small math related modification of the previous query like:

GROUP_SIZE = 2results = Entry.objects.annotate(row_number=Window(    expression=RowNumber(),    partition_by=[F('client')],    order_by=F('created').desc())).annotate(row_number=(F('row_number') - 1)/GROUP_SIZE + 1).order_by('row_number', 'client')for result in results:    print('Id: {} - client: {} - row_number {}'.format(result.id, result.client, result.row_number))

Output:

Id: 12 - client: facebook - row_number 1Id: 11 - client: facebook - row_number 1Id: 8 - client: google - row_number 1Id: 13 - client: google - row_number 1Id: 10 - client: facebook - row_number 2Id: 9 - client: facebook - row_number 2Id: 3 - client: google - row_number 2Id: 5 - client: google - row_number 2Id: 7 - client: facebook - row_number 3Id: 6 - client: facebook - row_number 3Id: 1 - client: google - row_number 3Id: 2 - client: google - row_number 3Id: 4 - client: facebook - row_number 4

You can notice that GROUP_SIZE constant defines how many items will be in the each alternating group.

P.S.

Thank you for asking this question because it helped me to better understand the Window Functions.

Happy coding :)

Thats not the most performant way, but works:

from itertools import zip_longestfrom django.db.models import Case, Whengrouped_pks = []for client in Entry.objects.values_list('client', flat=True).distinct():    grouped_pks.append(        Entry.objects.filter(client=client).values_list('pk', flat=True)    )alternated_pks = [    x for x in    filter(        None,        sum(zip_longest(*grouped_pks), ())    )]alternated_pks_order = Case(    *[        When(pk=pk, then=position)        for position, pk in enumerate(alternated_pks)    ])entries = Entry.objects.filter(pk__in=alternated_pks).order_by(alternated_pks_order)for entry in entries:    print('id: {} - client: {}'.format(entry.id, entry.client))

Expected output:

id: 8901 - client: googleid: 8890 - client: facebookid: 8884 - client: googleid: 8894 - client: facebookid: 8748 - client: googleid: 8891 - client: facebookid: 8906 - client: googleid: 8909 - client: facebookid: 8888 - client: googleid: 8895 - client: facebookid: 8919 - client: googleid: 8910 - client: facebookid: 8878 - client: googleid: 8896 - client: facebookid: 8916 - client: googleid: 8902 - client: facebookid: 8917 - client: googleid: 8885 - client: facebookid: 8918 - client: googleid: 8903 - client: facebookid: 8920 - client: googleid: 8886 - client: facebookid: 8904 - client: facebookid: 8905 - client: facebookid: 8887 - client: facebookid: 8911 - client: facebookid: 8897 - client: facebookid: 8912 - client: facebookid: 8898 - client: facebookid: 8899 - client: facebookid: 8914 - client: facebookid: 8900 - client: facebookid: 8915 - client: facebook

This is python3 code, but if you want to use it with python 2, change the zip_longest function to izip_longest.

This code is nice, because we still work with Queryset, so all other Sorting, Ordering, Managers, Pagination and other stuff will still work.

Maybe the following will work, with numpy module

import numpyqueryset = Entry.objects.all()''' catch the number of distinct Entry considering the client fields '''queryset_client_entries = Entry.objects.distinct('client')new_list = list(numpy.resize(queryset_client_entries, queryset.count())) ''' An alternative list of clients depending on the length of queryset '''