How to link Django requests with custom order saving

Question

I would like to add or link several Querysets in Django, preserving the order of each of them (and not the result). I use a third-party library to break down the result, and it only accepts lists or queries. I tried these options:

Queryset join : does not preserve order in separate queries, so I cannot use this.

result = queryset_1 | queryset_2 

Using itertools . Calling list() on a chained object actually evaluates the requests, and this can cause a lot of overhead. Is not it?

 result = list(itertools.chain(queryset_1, queryset_2)) 

Do you think I should go?

Answer 1

If the query sets have different models, you should evaluate them in lists, and then you can simply add:

 result = list(queryset_1) + list(queryset_2) 

If this is the same model, you must combine the queries using the Q object and 'order_by ("field queryset_1", "field queryset_2")'.

The correct answer largely depends on why you want to combine them and how you intend to use the results.

Answer 2

For Django 1.11 (released April 4, 2017) use union () for this, the documentation here:

https://docs.djangoproject.com/en/1.11/ref/models/querysets/#django.db.models.query.QuerySet.union

Here is the Version 2.1 link to this: https://docs.djangoproject.com/en/2.1/ref/models/querysets/#union

Answer 3

I am not 100% sure that this solution works in all possible cases, but it seems that the result is a combination of two QuerySets (in the same model) preserving the order of the first:

 union = qset1.union(qset2) union.query.extra_order_by = qset1.query.extra_order_by union.query.order_by = qset1.query.order_by union.query.default_ordering = qset1.query.default_ordering union.query.get_meta().ordering = qset1.query.get_meta().ordering 

I have not tested it comprehensively, so before using this code in a production environment, make sure that it works as expected.

Answer 4

If you need to combine two requests into a third request , here is an example using _result_cache .

model

 class ImportMinAttend(models.Model): country=models.CharField(max_length=2, blank=False, null=False) status=models.CharField(max_length=5, blank=True, null=True, default=None) 

From this model, I want to display a list of all rows in such a way that:

• (request 1) empty status first, sorted by country
• (request 2) non-empty status comes second, sorted by country

I want to combine query 1 and query 2.

  #get all the objects queryset=ImportMinAttend.objects.all() #get the first queryset queryset_1=queryset.filter(status=None).order_by("country") #len or anything that hits the database len(queryset_1) #get the second queryset queryset_2=queryset.exclude(status=None).order_by("country") #append the second queryset to the first one AND PRESERVE ORDER for query in queryset_2: queryset_1._result_cache.append(query) #final result queryset=queryset_1 

It may not be very effective, but it works :).

Answer 5

If two sets of queries have a common field, you can sort the combined set of queries by this field. Request sets are not evaluated during this operation.

For example:

 class EventsHistory(models.Model): id = models.IntegerField(primary_key=True) event_time = models.DateTimeField() event_id = models.IntegerField() class EventsOperational(models.Model): id = models.IntegerField(primary_key=True) event_time = models.DateTimeField() event_id = models.IntegerField() qs1 = EventsHistory.objects.all() qs2 = EventsOperational.objects.all() qs_combined = qs2.union(qs1).order_by('event_time') 

Answer 6

So, the Peter- inspired answer is what I did in my project (by the way, Django 2.2):

 from django.db import models from .models import MyModel # Add an extra field to each query with a constant value queryset_0 = MyModel.objects.annotate( qs_order=models.Value(0, models.IntegerField()) # Each constant should basically act as the position where we want the # queryset to stay queryset_1 = MyModel.objects.annotate( qs_order=models.Value(1, models.IntegerField()) [...] queryset_n = MyModel.objects.annotate( qs_order=models.Value(n, models.IntegerField()) # Finally, I ordered the union result by that extra field. union = queryset_0.union( queryset_1, queryset_2, [...], queryset_n).order_by('qs_order') 

At the same time, I could order the resulting union as I wanted, without changing any private attribute, while evaluating query sets only once.