CODEWITHSUNDEEP
pythondjangodjango-queryset
Caumons
Caumons

Reputation: 9595

How to chain Django querysets preserving individual order

I'd like to append or chain several Querysets in Django, preserving the order of each one (not the result). I'm using a third-party library to paginate the result, and it only accepts lists or querysets. I've tried these options:

Queryset join: Doesn't preserve ordering in individual querysets, so I can't use this.

result = queryset_1 | queryset_2

Using itertools: Calling list() on the chain object actually evaluates the querysets and this could cause a lot of overhead. Doesn't it?

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

How do you think I should go?

Upvotes: 33

Views: 15185

Answers (8)

Yurii Liubchenko
Yurii Liubchenko

Reputation: 159

This solution prevents duplicates:

q1 = Q(...)
q2 = Q(...)
q3 = Q(...)
qs = (
    Model.objects
    .filter(q1 | q2 | q3)
    .annotate(
        search_type_ordering=Case(
            When(q1, then=Value(2)),
            When(q2, then=Value(1)),
            When(q3, then=Value(0)),
            default=Value(-1),
            output_field=IntegerField(),
        )
    )
    .order_by('-search_type_ordering', ...)
)

Upvotes: 15

Original BBQ Sauce
Original BBQ Sauce

Reputation: 696

So, inspired by Peter's answer this is what I did in my project (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')

With this, I could order the resulting union as I wanted without changing any private attribute while only evaluating the querysets once.

Upvotes: 6

A.J.
A.J.

Reputation: 9025

the union() function to combine multiple querysets together, rather than the or (|) operator. This avoids a very inefficient OUTER JOIN query that reads the entire table.

Upvotes: 0

Martin K.
Martin K.

Reputation: 1127

For Django 1.11 (released on April 4, 2017) use union() for this, 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

Upvotes: 0

Peter Svac
Peter Svac

Reputation: 312

If two querysets has common field, you can order combined queryset by that field. Querysets 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')

Upvotes: -1

adonig
adonig

Reputation: 220

I'm not 100% sure this solution works in every possible case, but it looks like the result is the union of two QuerySets (on the same model) preserving the order of the first one:

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 did not test it extensively, so before you use that code in production, make sure it behaves like expected.

Upvotes: 1

rom
rom

Reputation: 3672

If you need to merge two querysets into a third queryset, 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 the rows such that :

  1. (query 1) empty status go first, ordered by countries
  2. (query 2) non empty status go in second, ordered by countries

I want to merge 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 might not be very efficient, but it works :).

Upvotes: 0

Demiurge
Demiurge

Reputation: 101

If the querysets are of different models, you have to evaluate them to lists and then you can just append:

result = list(queryset_1) + list(queryset_2)

If they are the same model, you should combine the queries using the Q object and 'order_by("queryset_1 field", "queryset_2 field")'.

The right answer largely depends on why you want to combine these and how you are going to use the results.

Upvotes: 10

Related Questions