Skip to content

Django Querying

Get

try:
    user = User.objects.get(name="name")
except User.DoesNotExist:
    do_something()

You should use filter if you want to get multiple objects

result = User.objects.filter(name="name")
if not result:
    do_something()
user = result.first()

get_or_create()

obj, created = Person.objects.get_or_create(
    first_name='John',
    last_name='Lennon',
    defaults={'birthday': date(1940, 10, 9)},
)

Filtering

SQL where id in [1,3,4,5,6....];

.filter(id__in=[1, 3, 4, 5, 6....])
model_name.filter().values()

.filter(id=1, name=1) and .filter(id=1).filter(name=1)

  • they only might be different when dealing with foreign keys

Set a foreign key to be something other than an ID

class DashboardWidget(models.Model):
    query = models.ForeignKey(
        DashboardQuery,  # the foreign model
        to_field="file_path",  # DashboardQuery.file_path, must be unique
        db_column="query_file_path",  # name of the db column, default: query_id (field-name_id)
        ...
    )

Doing a migration? - you need to drop the DashboardWidget.query and then re-create it - AlterField doesn't change the column type from int to text in Django 2.2

Q Objects

Useful for OR in your WHERE clause

Django 4.1 added XOR

  • .objects.filter(a=1, b=1) ANDs things together
SELECT *
FROM Poll
WHERE question LIKE "Who%" OR question LIKE "What%"
from django.db.models import Q

lookup = Q(question__startswith='Who') | Q(question__startswith='What')
Poll.objects.filter(lookup)

F Expressions

Field Expression

Get the value of that column

SELECT price * 1.13
FROM Product
from django.db.models import F
Product.objects.filter(taxed_price=F('price') * 1.13)

Get the instances from a QuerySet?

  • Convert it into a list and it will make the database call
list(Widget.objects.filter())
  1. startproject and startapp

UUIDField as primary key

  • cluster index?
    • don't use UUIDs
  AGE_RATING =[
    ("G","General Audiences"),
    ("PG","Parental Guidance Suggested"),
    ("PG-13","Inappropriate for Children Under 13" ),
    ("R", "Restricted"),
    ("NC-17","Adults Only"),
  ]

age_rating = models.CharField(max_length= 5, choices = AGE_RATING, default = "GENERAL AUDIENCE")

Aggregation

To use the aggregation functions, you need

SELECT SUM(population)
FROM City
from django.db.models import Sum, Min, Max, Avg

>>> City.objects.aggregate(Sum('population'))
{'population_sum': 1234567}

Annotation

Adding a new column to the result

SELECT population * 1.05 AS "future_population"
FROM City
>>> result = City.objects.annotate(future_population=F('population') * 1.05)
>>> result.future_population
12345

Inner join for a 1 → n foreign key

Fetch the foreign keys

Order.objects.select_related('customer').all()
SELECT *
FROM Order o
  INNER JOIN Customer c
    ON (o.customer_id = c.customer_id)

Many to Many relationship

Order.objects.prefetch_related('products').all()

OrderProduct is the many to many Order ↔ Product table

WITH order_ids AS (
  SELECT id
  FROM Order
)
SELECT *
FROM Product
  INNER JOIN OrderProduct
    ON Product.id = OrderProduct.product_id
WHERE OrderProduct.order_id IN (SELECT id FROM order_ids)

Many-to-many

Create an entry in a many

my_obj.categories.add(fragmentCategory.objects.get(id=1))
my_obj.categories.create(name='val1')
my_obj.categories.add(fragmentCategory.objects.get(id=1))

List many to many

user.groups.all()

Last update: 2022-09-23