Query Data in Django Model

Setting Up a Django Model

First, ensure you have a Django model defined. For example, let’s say you have a model called Book:

from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.CharField(max_length=100)
    published_date = models.DateField()
    isbn = models.CharField(max_length=13, unique=True)

    def __str__(self):
        return self.title

Querying Data

1. Retrieving All Records

To retrieve all records of the Book model:

books = Book.objects.all()

2. Filtering Records

To filter records, you can use the filter method. For example, to get all books by a specific author:

books_by_author = Book.objects.filter(author='John Doe')

You can also chain multiple filters:

recent_books = Book.objects.filter(author='John Doe', published_date__year=2023)

3. Retrieving a Single Record

To retrieve a single record, you can use the get method. This is useful when you are sure the query will return only one result:

book = Book.objects.get(isbn='1234567890123')

Note: get will raise a DoesNotExist exception if no record is found, and a MultipleObjectsReturned exception if more than one record is found.

4. Excluding Records

To exclude certain records, use the exclude method:

non_recent_books = Book.objects.exclude(published_date__year=2023)

5. Ordering Records

To order records, use the order_by method:

ordered_books = Book.objects.order_by('published_date')

To order in descending order:

ordered_books_desc = Book.objects.order_by('-published_date')

6. Limiting Querysets

To limit the number of results returned, you can use Python’s array slicing:

limited_books = Book.objects.all()[:10]

7. Aggregating Data

To perform aggregation, use the aggregate method along with Django’s aggregation functions like Count, Max, Min, Avg, and Sum:

from django.db.models import Avg

average_published_year = Book.objects.aggregate(Avg('published_date__year'))

8. Using Q Objects for Complex Queries

For more complex queries, such as OR conditions, use Q objects:

from django.db.models import Q

books = Book.objects.filter(Q(author='John Doe') | Q(author='Jane Doe'))

9. Raw SQL Queries

If needed, you can execute raw SQL queries:

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("SELECT * FROM myapp_book WHERE author = %s", ['John Doe'])
    books = cursor.fetchall()

Or use raw manager method:

books = Book.objects.raw('SELECT * FROM myapp_book WHERE author = %s', ['John Doe'])

References
https://docs.djangoproject.com/en/5.0/topics/db/queries/