1. The 13 Essential QuerySet Methods

# all(): Retrieve all objects
# filter(**kwargs): Return objects matching given filter criteria
# get(**kwargs): Return a single object matching criteria; raises error if none or multiple found
# exclude(**kwargs): Return objects not matching filter criteria
# values(*field): Return a QuerySet of dictionaries (iterable)
# values_list(*field): Return a QuerySet of tuples
# order_by(*field): Sort results
# reverse(): Reverse the ordering of a QuerySet (requires defined ordering)
# distinct(): Remove duplicate rows from results (field-level distinct only in PostgreSQL)
# count(): Return number of objects matching query
# first(): Return the first record
# last(): Return the last record
# exists(): Return True if QuerySet contains any data, else False

Methods Returning QuerySet Objects:

• all(), filter(), exclude(), order_by(), reverse(), distinct()

Special QuerySet Returns:

• values() returns iterable dictionaries
• values_list() returns iterable tuples

Methods Returning Single Objects:

• get(), first(), last()

Methods Returning Boolean:

• exists()

Methods Returning Number:

• count()

2. Field Lookups with Double Underscore

# Greater than and less than
models.Tb1.objects.filter(id__lt=10, id__gt=1)  # id > 1 AND id < 10

# IN and NOT IN
models.Tb1.objects.filter(id__in=[11, 22, 33])  # id in (11,22,33)
models.Tb1.objects.exclude(id__in=[11, 22, 33])  # id NOT in (11,22,33)

# Contains (case-sensitive and case-insensitive)
models.Tb1.objects.filter(name__contains="ven")  # name like '%ven%'
models.Tb1.objects.filter(name__icontains="ven")  # case-insensitive

# Range (equivalent to BETWEEN AND)
models.Tb1.objects.filter(id__range=[1, 3])  # id between 1 and 3

# Other string lookups: startswith, istartswith, endswith, iendswith

# Date field lookups
models.Class.objects.filter(first_day__year=2017)

3. ForeignKey Operations

Forward Lookup

• Object-based: obj.related_field.field

  book_obj = models.Book.objects.first()
  print(book_obj.publisher)  # Publisher object
  print(book_obj.publisher.name)  # Publisher name
  

• Field-based: related_field__field

  print(models.Book.objects.values_list("publisher__name"))
  

Reverse Lookup

• Object-based: obj.modelname_set

  publisher_obj = models.Publisher.objects.first()
  books = publisher_obj.book_set.all()  # All books by this publisher
  

• Feild-based: modelname__field

  titles = models.Publisher.objects.values_list("book__title")
  

4. ManyToManyField and RelatedManager

The RelatedManager is used in one-to-many or many-to-many contexts when the related object may have multiple instances.

Available Methods:

• create(): Create a new object, save it, and add it to the related object set.

  import datetime
  models.Author.objects.first().book_set.create(title="Tomato Story", publish_date=datetime.date.today())
  

• add(): Add specified model objects or IDs to the related set.

  # Add objects
  author_objs = models.Author.objects.filter(id__lt=3)
  models.Book.objects.first().authors.add(*author_objs)
  # Add IDs
  models.Book.objects.first().authors.add(*[1, 2])
  

• set(): Update the related object set.

  book_obj = models.Book.objects.first()
  book_obj.authors.set([2, 3])
  

• remove(): Remove specified model objects from the related set.

  book_obj = models.Book.objects.first()
  book_obj.authors.remove(3)
  

• clear(): Remove all objects from the related set.

  book_obj = models.Book.objects.first()
  book_obj.authors.clear()
  

Note for ForeignKey: clear() and remove() are only available when null=True is set on the ForeignKey field.

# Without null=True - no clear/remove
class Book(models.Model):
    title = models.CharField(max_length=32)
    publisher = models.ForeignKey(to=Publisher)
# AttributeError: 'RelatedManager' object has no attribute 'clear'

# With null=True - clear/remove available
class Book(models.Model):
    name = models.CharField(max_length=32)
    publisher = models.ForeignKey(to=Class, null=True)
# Works fine

All related manager methods (add(), create(), remove(), clear(), set()) immediately update the database; no extra save() call is needed.

5. Aggregation and Grouping

Aggregation

aggregate() is a terminal clause that returns a dictionary of key-value pairs.

from django.db.models import Avg, Sum, Max, Min, Count

models.Book.objects.all().aggregate(Avg("price"))
# {'price__avg': 13.233333}

# Named aggregation
models.Book.objects.aggregate(average_price=Avg('price'))
# {'average_price': 13.233333}

# Multiple aggregations
models.Book.objects.all().aggregate(Avg("price"), Max("price"), Min("price"))
# {'price__avg': 13.233333, 'price__max': Decimal('19.90'), 'price__min': Decimal('9.90')}

Grouping (Group By)

Using annotate() to group by a field.

from django.db.models import Avg

# Group by department
Employee.objects.values("dept").annotate(avg=Avg("salary")).values("dept", "avg")

# Group by related table
from django.db.models import Avg
models.Dept.objects.annotate(avg=Avg("employee__salary")).values("name", "avg")

More Examples:

• Count authors per book:

  book_list = models.Book.objects.all().annotate(author_num=Count("author"))
  

• Minimum book price per publishre:

  publisher_list = models.Publisher.objects.annotate(min_price=Min("book__price"))
  # Or
  models.Book.objects.values("publisher__name").annotate(min_price=Min("price"))
  

• Books with more than one author:

  models.Book.objects.annotate(author_num=Count("author")).filter(author_num__gt=1)
  

• Order books by author count:

  models.Book.objects.annotate(author_num=Count("author")).order_by("author_num")
  

• Total price of books per author:

  models.Author.objects.annotate(sum_price=Sum("book__price")).values("name", "sum_price")
  

6. F Expressions and Q Expressions

F Expressions

Use F() to compare two fields of the same model instance or perform arithmetic.

from django.db.models import F

# Comparison
models.Book.objects.filter(comment_num__gt=F('keep_num'))

# Arithmetic
models.Book.objects.filter(comment_num__lt=F('keep_num') * 2)

# Update
models.Book.objects.all().update(price=F("price") + 30)

# Updating char fields
from django.db.models.functions import Concat
from django.db.models import Value
models.Book.objects.all().update(title=Concat(F("title"), Value("("), Value("First Edition"), Value(")")))

Q Expressions

Use Q objects for complex queries like OR or NOT.

from django.db.models import Q

# OR query
models.Book.objects.filter(Q(authors__name="Fairy") | Q(authors__name="Witch"))

# AND and NOT
models.Book.objects.filter(Q(author__name="Fairy") & ~Q(publish_date__year=2018)).values_list("title")

# Mixing Q with keyword arguments (Q must come first)
models.Book.objects.filter(
    Q(publish_date__year=2018) | Q(publish_date__year=2017),
    title__icontains="story"
)

7. Transactions

import os

if __name__ == '__main__':
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "BMS.settings")
    import django
    django.setup()

    import datetime
    from app01 import models

    try:
        from django.db import transaction
        with transaction.atomic():
            new_publisher = models.Publisher.objects.create(name="Mars Press")
            models.Book.objects.create(
                title="Orange Story",
                publish_date=datetime.date.today(),
                publisher_id=10  # Intentionally invalid ID
            )
    except Exception as e:
        print(str(e))

8. Lesser-Known Operations

Executing Raw SQL

• Using extra():

  models.UserInfo.objects.extra(
      select={'newid': 'select count(1) from app01_usertype where id>%s'},
      select_params=[1,],
      where=['age>%s'],
      params=[18,],
      order_by=['-age'],
      tables=['app01_usertype']
  )
  

• Using raw cursor:

  from django.db import connection, connections
  cursor = connection.cursor()  # or connections['default'].cursor()
  cursor.execute("""SELECT * from auth_user where id = %s""", [1])
  row = cursor.fetchone()
  

Important QuerySet Methods

• select_related(*fields): Perform JOINs for one-to-one and many-to-one relationships to reduce queries.
• prefetch_related(*lookups): Optimize many-to-many and one-to-many queries by executing separate SQL queries and joining in Python.
• defer(*fields): Exclude certain columns from the query.
• only(*fields): Only load specified columns.
• using(alias): Specify which database to use (based on settings).
• raw(raw_query, ...): Execute raw SQL and return model instances.
• bulk_create(objs, batch_size=None): Insert multiple objects in one query.
• get_or_create(defaults=None, **kwargs): Retreive or create an object.
• update_or_create(defaults=None, **kwargs): Update or create an object.
• in_bulk(id_list=None): Look up objects by primary key list.
• dates(field_name, kind, order='ASC'): Retrieve distinct date parts.
• datetimes(field_name, kind, order='ASC', tzinfo=None): Similar but with timezone support.

9. Logging SQL Queries in Django Terminal

Add the following to your Django project's settings.py:

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level': 'DEBUG',
        },
    },
}

This will print the translated SQL statements to the console.

10. Running Django ORM in a Standalone Python Script

import os

if __name__ == '__main__':
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "BMS.settings")
    import django
    django.setup()

    from app01 import models

    books = models.Book.objects.all()
    print(books)