How to combine multiple QuerySets in Django?

QuerySets allow you to filter, order, and manipulate data from your database using a high-level Pythonic syntax. However, there are situations where you may need to combine multiple QuerySets into a single QuerySet to work with the data more efficiently. This article will explore various methods to achieve this in Django.

What is a QuerySet in Django?

Before combining QuerySets, let’s have a quick refresher on what a QuerySet is in Django. A QuerySet is a collection of database queries to retrieve data from your database. It represents a set of records from a database table or a result of a database query. Query sets are lazy, meaning they are not evaluated until you explicitly request the data, which makes them highly efficient.

The chain function from itertools

In this example, we first import the chain function from itertools. Then, we retrieve the QuerySets for both the Author and Book models using objects.all(). Finally, we use a chain to combine these QuerySets and convert the result into a list.

Python3

from itertools import chain
from .models import Author, Book

authors = Author.objects.all()
books = Book.objects.all()

combined_queryset = list(chain(authors, books))

Using Union Method

In this example, we have two QuerySets of authors from the USA and the UK. We use the union method to combine them into a single QuerySet.

Python3

from .models import Author

authors1 = Author.objects.filter(country='USA')
authors2 = Author.objects.filter(country='UK')

combined_queryset = authors1.union(authors2)

Using Q Objects Method

In this example, we use Q objects to create OR conditions for authors from the USA and the UK. We also filter books published after the year 2000. Then, we combine the QuerySets using the chain function.

Python3

from .models import Author, Book
from django.db.models import F

authors = Author.objects.annotate(book_count=F('book__count'))
books = Book.objects.annotate(author_name=F('author__name'))

combined_queryset = list(chain(authors, books))

Create a project to apply combine multiple QuerySets in Django

Creating a simple Django project to demonstrate Method 3 (Using Q Objects) for combining QuerySets involves setting up a project with models, views, templates, and URLs. In this example, we’ll create a project named “bookstore” with two models: Author and Book. We’ll create a view to display a list of authors from the USA or the UK and books published after the year 2000.

Note – in this we will only use In-build method (Union and Q Method) to combine multiple QuerySets

Step 1 : Create a project folder, and Navigate to the project directory

django-admin startproject bookstore
cd bookstore

Step 2: Create a Django App

Inside your project, create a Django app named “mini”:

python manage.py startapp mini

Step 3 : Create model

Edit the models.py file in the “mini” app to define the Author and Book models:

Python3

# mini/models.py
from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)
    country = models.CharField(max_length=50)

    def __str__(self):
        return self.name

class Book(models.Model):
    title = models.CharField(max_length=100)
    published_year = models.IntegerField()
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

    def __str__(self):
        return self.title

Step 4 : Create views

This view retrieves authors from two different countries, the USA and India, using two separate QuerySets (authors1 and authors2). It then uses the union method to combine these QuerySets into a single QuerySet named authors_and_books. This QuerySet will contain distinct author records from both countries. Finally, it renders an HTML template named ‘myapp/index.html’ and passes the authors_and_books QuerySet to the template.

Note, The code also includes commented-out code that demonstrates an alternative way to combine QuerySets using Q objects. To use this method, you can uncomment the authors_and_books line that is currently commented out and comment the authors_and_books line using union.

Python3

# mini/views.py
from django.shortcuts import render
from django.db.models import Q
from .models import Author, Book
from django.http import HttpResponse

def home(request):
    return HttpResponse('hello world')

def author_and_books(request):
    authors1 = Author.objects.filter(country='USA')
    authors2 = Author.objects.filter(country='India')

    # using union
    authors_and_books = authors1.union(authors2)

    ## Using Q Method
    # authors_and_books = list(
    # Author.objects.filter(Q(country='USA') | Q(country='UK')) )

    return render(request, 'myapp/index.html',
                  {'authors_and_books': authors_and_books})

Step 5 : Create template(template/index.html)

Create HTML templates for rendering the list of authors and books.

HTML

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Authors and Books</title>
</head>
<body>
    <h1>Authors and Books</h1>
    <ul>
        {% for item in authors_and_books %}
            <li>{{ item }}</li>
        {% endfor %}
    </ul>
</body>
</html>

Step 6: Config URLs

Configure the URL routing for the “mini” app. Create a urls.py file inside the “mini” app directory with the following content.

Python3

# mini/urls.py
from django.urls import path
from . import views

urlpatterns = [
    path('', views.home, name='home'),
    path('authors-and-books/', views.author_and_books,
         name='author_and_books'),
]

Step 7: Confif project URLs

Include the “mini” app’s URLs in the project’s URL configuration. Open the urls.py file in your project directory and update it as follows:

Python3

from django.contrib import admin
from django.urls import path, include

urlpatterns = [
    path('admin/', admin.site.urls),
    path('', include('mini.urls')),
]

create a super user and add some data from the admin dashboard. make sure to register your model in admin.py.

Run migration file

python manage.py makemigrations
python manage.py migrate

Deploy

python manage.py runserver

Output: