How to combine multiple QuerySets in Django?

Question

I'm trying to build the search for a Django site I am building, and in that search, I am searching across three different models. And to get pagination on the search result list, I would like to use a generic object_list view to display the results. But to do that, I have to merge three QuerySets into one.

How can I do that? I've tried this:

result_list = []
page_list = Page.objects.filter(
    Q(title__icontains=cleaned_search_term) |
    Q(body__icontains=cleaned_search_term))
article_list = Article.objects.filter(
    Q(title__icontains=cleaned_search_term) |
    Q(body__icontains=cleaned_search_term) |
    Q(tags__icontains=cleaned_search_term))
post_list = Post.objects.filter(
    Q(title__icontains=cleaned_search_term) |
    Q(body__icontains=cleaned_search_term) |
    Q(tags__icontains=cleaned_search_term))

for x in page_list:
    result_list.append(x)
for x in article_list:
    result_list.append(x)
for x in post_list:
    result_list.append(x)

return object_list(
    request,
    queryset=result_list,
    template_object_name='result',
    paginate_by=10,
    extra_context={
        'search_term': search_term},
    template_name="search/result_list.html")

But this doesn't work. I get an error when I try to use that list in the generic view. The list is missing the clone attribute.

How can I merge the three lists, page_list, article_list and post_list?

Answer 1

This can be achieved by two ways either.

1st way to do this

Use union operator for QuerySet | to take union of two QuerySets. If both QuerySets belongs to the same model / a single model then it is possible to combine QuerySets by using the union operator.

For an instance

pagelist1 = Page.objects.filter(
    Q(title__icontains=cleaned_search_term) | 
    Q(body__icontains=cleaned_search_term))
pagelist2 = Page.objects.filter(
    Q(title__icontains=cleaned_search_term) | 
    Q(body__icontains=cleaned_search_term))
combined_list = pagelist1 | pagelist2 # this would take union of two querysets

2nd way to do this

One other way to achieve combine operations between two QuerySets is to use the itertools chain function.

from itertools import chain
combined_results = list(chain(pagelist1, pagelist2))

Answer 2

Concatenating the querysets into a list is the simplest approach. If the database will be hit for all querysets anyway (e.g. because the result needs to be sorted), this won't add further cost.

from itertools import chain
result_list = list(chain(page_list, article_list, post_list))

Using itertools.chain is faster than looping each list and appending elements one by one, since itertools is implemented in C. It also consumes less memory than converting each queryset into a list before concatenating.

Now it's possible to sort the resulting list e.g. by date (as requested in hasen j's comment to another answer). The sorted() function conveniently accepts a generator and returns a list:

from operator import attrgetter
result_list = sorted(
    chain(page_list, article_list, post_list),
    key=attrgetter('date_created')
)

You can reverse the sort order:

result_list = sorted(
    chain(page_list, article_list, post_list),
    key=attrgetter('date_created'),
    reverse=True,
)

attrgetter is equivalet to the following lambda (this was the way it had to be done before Python 2.4):

result_list = sorted(
    chain(page_list, article_list, post_list),
    key=lambda instance: instance.date_created,
)

Answer 3

To get the intersection of both querysets:

result = first_queryset.intersection(second_queryset)

Answer 4

You can use "|"(bitwise or) to combine the querysets of the same model as shown below:

# "store/views.py"

from .models import Food
from django.http import HttpResponse
                                                
def test(request):
                                             # ↓ Bitwise or
    result = Food.objects.filter(name='Apple') | Food.objects.filter(name='Orange')
    print(result)
    return HttpResponse("Test")

Output on console:

<QuerySet [<Food: Apple>, <Food: Orange>]>
[22/Jan/2023 12:51:44] "GET /store/test/ HTTP/1.1" 200 9

And, you can use |= to add the queryset of the same model as shown below:

# "store/views.py"

from .models import Food
from django.http import HttpResponse
                                                
def test(request):
    result = Food.objects.filter(name='Apple')
         # ↓↓ Here
    result |= Food.objects.filter(name='Orange')
    print(result)
    return HttpResponse("Test")

Output on console:

<QuerySet [<Food: Apple>, <Food: Orange>]>
[22/Jan/2023 12:51:44] "GET /store/test/ HTTP/1.1" 200 9

Be careful, if adding the queryset of a different model as shown below:

# "store/views.py"

from .models import Food, Drink
from django.http import HttpResponse
                                                
def test(request):
          # "Food" model                      # "Drink" model
    result = Food.objects.filter(name='Apple') | Drink.objects.filter(name='Milk')
    print(result)
    return HttpResponse("Test")

There is an error below:

AssertionError: Cannot combine queries on two different base models.
[22/Jan/2023 13:40:54] "GET /store/test/ HTTP/1.1" 500 96025

But, if adding the empty queryset of a different model as shown below:

# "store/views.py"

from .models import Food, Drink
from django.http import HttpResponse
                                                
def test(request):
          # "Food" model                       # Empty queryset of "Drink" model 
    result = Food.objects.filter(name='Apple') | Drink.objects.none()
    print(result)
    return HttpResponse("Test")

There is no error below:

<QuerySet [<Food: Apple>]>
[22/Jan/2023 13:51:09] "GET /store/test/ HTTP/1.1" 200 9

Again be careful, if adding the object by get() as shown below:

# "store/views.py"

from .models import Food
from django.http import HttpResponse
                                                
def test(request):
    result = Food.objects.filter(name='Apple')
                         # ↓↓ Object
    result |= Food.objects.get(name='Orange')
    print(result)
    return HttpResponse("Test")

There is an error below:

AttributeError: 'Food' object has no attribute '_known_related_objects'
[22/Jan/2023 13:55:57] "GET /store/test/ HTTP/1.1" 500 95748

Answer 5

The best option is to use the Django built-in methods:

# Union method
result_list = page_list.union(article_list, post_list)

That will return the union of all the objects in those querysets.

If you want to get just the objects that are in the three querysets, you will love the built-in method of querysets, intersection.

# intersection method
result_list = page_list.intersection(article_list, post_list)

Answer 6

You can use Union:

qs = qs1.union(qs2, qs3)

But if you want to apply order_by on the foreign models of the combined queryset... then you need to Select them beforehand this way... otherwise it won't work.

Example

qs = qs1.union(qs2.select_related("foreignModel"), qs3.select_related("foreignModel"))
qs.order_by("foreignModel__prop1")

where prop1 is a property in the foreign model.

Answer 7

This will do the work without using any other libraries:

result_list = page_list | article_list | post_list

Answer 8

Related, for mixing querysets from the same model, or for similar fields from a few models, starting with Django 1.11 a QuerySet.union() method is also available:

union()

union(*other_qs, all=False)

New in Django 1.11. Uses SQL’s UNION operator to combine the results of two or more QuerySets. For example:

>>> qs1.union(qs2, qs3)

The UNION operator selects only distinct values by default. To allow duplicate values, use the all=True argument.

union(), intersection(), and difference() return model instances of the type of the first QuerySet even if the arguments are QuerySets of other models. Passing different models works as long as the SELECT list is the same in all QuerySets (at least the types, the names don’t matter as long as the types in the same order).

In addition, only LIMIT, OFFSET, and ORDER BY (i.e. slicing and order_by()) are allowed on the resulting QuerySet. Further, databases place restrictions on what operations are allowed in the combined queries. For example, most databases don’t allow LIMIT or OFFSET in the combined queries.

Answer 9

Here's an idea... just pull down one full page of results from each of the three and then throw out the 20 least useful ones... this eliminates the large querysets and that way you only sacrifice a little performance instead of a lot.

Answer 10

Try this:

matches = pages | articles | posts

It retains all the functions of the querysets which is nice if you want to order_by or similar.

Please note: this doesn't work on querysets from two different models.

Answer 11

This recursive function concatenates array of querysets into one queryset.

def merge_query(ar):
    if len(ar) ==0:
        return [ar]
    while len(ar)>1:
        tmp=ar[0] | ar[1]
        ar[0]=tmp
        ar.pop(1)
        return ar

Answer 12

Requirements: Django==2.0.2, django-querysetsequence==0.8

In case you want to combine querysets and still come out with a QuerySet, you might want to check out django-queryset-sequence.

But one note about it. It only takes two querysets as it's argument. But with python reduce you can always apply it to multiple querysets.

from functools import reduce
from queryset_sequence import QuerySetSequence

combined_queryset = reduce(QuerySetSequence, list_of_queryset)

And that's it. Below is a situation I ran into and how I employed list comprehension, reduce and django-queryset-sequence

from functools import reduce
from django.shortcuts import render    
from queryset_sequence import QuerySetSequence

class People(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    mentor = models.ForeignKey('self', null=True, on_delete=models.SET_NULL, related_name='my_mentees')

class Book(models.Model):
    name = models.CharField(max_length=20)
    owner = models.ForeignKey(Student, on_delete=models.CASCADE)

# as a mentor, I want to see all the books owned by all my mentees in one view.
def mentee_books(request):
    template = "my_mentee_books.html"
    mentor = People.objects.get(user=request.user)
    my_mentees = mentor.my_mentees.all() # returns QuerySet of all my mentees
    mentee_books = reduce(QuerySetSequence, [each.book_set.all() for each in my_mentees])

    return render(request, template, {'mentee_books' : mentee_books})

Answer 13

DATE_FIELD_MAPPING = {
    Model1: 'date',
    Model2: 'pubdate',
}

def my_key_func(obj):
    return getattr(obj, DATE_FIELD_MAPPING[type(obj)])

And then sorted(chain(Model1.objects.all(), Model2.objects.all()), key=my_key_func)

Quoted from https://groups.google.com/forum/#!topic/django-users/6wUNuJa4jVw. See Alex Gaynor

Answer 14

In case you want to chain a lot of querysets, try this:

from itertools import chain
result = list(chain(*docs))

where: docs is a list of querysets

Answer 15

You can use the QuerySetChain class below. When using it with Django's paginator, it should only hit the database with COUNT(*) queries for all querysets and SELECT() queries only for those querysets whose records are displayed on the current page.

Note that you need to specify template_name= if using a QuerySetChain with generic views, even if the chained querysets all use the same model.

from itertools import islice, chain

class QuerySetChain(object):
    """
    Chains multiple subquerysets (possibly of different models) and behaves as
    one queryset.  Supports minimal methods needed for use with
    django.core.paginator.
    """

    def __init__(self, *subquerysets):
        self.querysets = subquerysets

    def count(self):
        """
        Performs a .count() for all subquerysets and returns the number of
        records as an integer.
        """
        return sum(qs.count() for qs in self.querysets)

    def _clone(self):
        "Returns a clone of this queryset chain"
        return self.__class__(*self.querysets)

    def _all(self):
        "Iterates records in all subquerysets"
        return chain(*self.querysets)

    def __getitem__(self, ndx):
        """
        Retrieves an item or slice from the chained set of results from all
        subquerysets.
        """
        if type(ndx) is slice:
            return list(islice(self._all(), ndx.start, ndx.stop, ndx.step or 1))
        else:
            return islice(self._all(), ndx, ndx+1).next()

In your example, the usage would be:

pages = Page.objects.filter(Q(title__icontains=cleaned_search_term) |
                            Q(body__icontains=cleaned_search_term))
articles = Article.objects.filter(Q(title__icontains=cleaned_search_term) |
                                  Q(body__icontains=cleaned_search_term) |
                                  Q(tags__icontains=cleaned_search_term))
posts = Post.objects.filter(Q(title__icontains=cleaned_search_term) |
                            Q(body__icontains=cleaned_search_term) | 
                            Q(tags__icontains=cleaned_search_term))
matches = QuerySetChain(pages, articles, posts)

Then use matches with the paginator like you used result_list in your example.

The itertools module was introduced in Python 2.3, so it should be available in all Python versions Django runs on.

Answer 16

The big downside of your current approach is its inefficiency with large search result sets, as you have to pull down the entire result set from the database each time, even though you only intend to display one page of results.

In order to only pull down the objects you actually need from the database, you have to use pagination on a QuerySet, not a list. If you do this, Django actually slices the QuerySet before the query is executed, so the SQL query will use OFFSET and LIMIT to only get the records you will actually display. But you can't do this unless you can cram your search into a single query somehow.

Given that all three of your models have title and body fields, why not use model inheritance? Just have all three models inherit from a common ancestor that has title and body, and perform the search as a single query on the ancestor model.