9. ORM

9.1. QuerySet

  • sklejanie zapytań
  • Tworzenie obiektów
  • Zapisywanie save() ForeignKey
Model.save()
Model.objects.all()
Model.objects.get()
Model.objects.filter()
Model.objects.exclude()
Model.objects.distinct()
Model.objects.get().sql
Model.objects.all()[:5]
Model.objects.all()[5:10]
Model.objects.order_by('headline')[0]

9.1.1. Filtered QuerySets are unique

q1 = Entry.objects.filter(headline__startswith="What")
q2 = q1.exclude(pub_date__gte=datetime.date.today())
q3 = q1.filter(pub_date__gte=datetime.date.today())

9.1.2. QuerySets are lazy

q = Entry.objects.filter(headline__startswith="What")
q = q.filter(pub_date__lte=datetime.date.today())
q = q.exclude(body_text__icontains="food")
print(q)

9.2. Field lookups

Model.objects.filter(pub_date__lte='1969-07-24')
Model.objects.get(title__exact='Man walk on Moon!')
Model.objects.get(title__iexact='man walk on moon!')
Model.objects.get(headline__contains='Moon')
Model.objects.filter(title__startswith='Imporntant')
Model.objects.filter(title__istartswith='Imporntant')
Model.objects.filter(title__endswith='Imporntant')
Model.objects.filter(title__iendswith='Imporntant')

9.2.1. Lookups that span relationships

Entry.objects.filter(blog__name='Beatles Blog')
Blog.objects.filter(entry__headline__contains='Lennon')
Blog.objects.filter(entry__authors__name='Lennon')
Blog.objects.filter(entry__authors__name__isnull=True)
Blog.objects.exclude(
    entry__headline__contains='Lennon',
    entry__pub_date__year=2008,
)
Blog.objects.exclude(
    entry__in=Entry.objects.filter(
        headline__contains='Lennon',
        pub_date__year=2008,
    ),
)

9.3. Filters can reference fields on the model

from django.db.models import F
Entry.objects.filter(n_comments__gt=F('n_pingbacks'))
Entry.objects.filter(n_comments__gt=F('n_pingbacks') * 2)
Entry.objects.filter(rating__lt=F('n_comments') + F('n_pingbacks'))
Entry.objects.filter(authors__name=F('blog__name'))
from datetime import timedelta
Entry.objects.filter(mod_date__gt=F('pub_date') + timedelta(days=3))

9.3.1. The pk lookup shortcut

Blog.objects.get(id__exact=14)  # Explicit form
Blog.objects.get(id=14)         # __exact is implied
Blog.objects.get(pk=14)         # pk implies id__exact

# Get blogs entries with id 1, 4 and 7
Blog.objects.filter(pk__in=[1,4,7])

# Get all blog entries with id > 14
Blog.objects.filter(pk__gt=14)

# pk lookups also work across joins
Entry.objects.filter(blog__id__exact=3) # Explicit form
Entry.objects.filter(blog__id=3)        # __exact is implied
Entry.objects.filter(blog__pk=3)        # __pk implies __id__exact

9.4. Complex lookups with Q objects

from django.db.models import Q
Q(question__startswith='What')
Q(question__startswith='Who') | Q(question__startswith='What')
# WHERE question LIKE 'Who%' OR question LIKE 'What%'

Poll.objects.get(
    Q(question__startswith='Who'),
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)
# SELECT * from polls WHERE question LIKE 'Who%'
# AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')

9.5. Comparing objects

some_entry == other_entry
some_entry.id == other_entry.id

some_obj == other_obj
some_obj.name == other_obj.name

9.6. Q() expressions

from django.db.models import Q


Q(question__startswith='What')

Q(question__startswith='Who') | Q(question__startswith='What')
Q(question__startswith='Who') | ~Q(pub_date__year=2005)     # negated query
Poll.objects.get(
    Q(question__startswith='Who'),
    Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)

9.7. F() expressions

An F() object represents the value of a model field or annotated column. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory.

Iris.objects.all().update(petal_length=F('petal_length') + 1)

9.8. Aggregations

Code Listing 9.7. Django ORM Cheat sheet
# Total number of books.
Book.objects.count()
# 2452

# Total number of books with publisher=BaloneyPress
Book.objects.filter(publisher__name='BaloneyPress').count()
# 73

# Average price across all books.
from django.db.models import Avg
Book.objects.all().aggregate(Avg('price'))
# {'price__avg': 34.35}

# Max price across all books.
from django.db.models import Max
Book.objects.all().aggregate(Max('price'))
# {'price__max': Decimal('81.20')}

from django.db.models import Avg, Max, Min
Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
# {'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

# Difference between the highest priced book and the average price of all books.
from django.db.models import FloatField
Book.objects.aggregate(price_diff=Max('price', output_field=FloatField()) - Avg('price'))
# {'price_diff': 46.85}

# All the following queries involve traversing the Book<->Publisher
# foreign key relationship backwards.

# Each publisher, each with a count of books as a "num_books" attribute.
from django.db.models import Count
pubs = Publisher.objects.annotate(num_books=Count('book'))
# <QuerySet [<Publisher: BaloneyPress>, <Publisher: SalamiPress>, ...]>
pubs[0].num_books
# 73

# Each publisher, with a separate count of books with a rating above and below 5
from django.db.models import Q
above_5 = Count('book', filter=Q(book__rating__gt=5))
below_5 = Count('book', filter=Q(book__rating__lte=5))
pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5)
pubs[0].above_5
# 23
pubs[0].below_5
# 12

# The top 5 publishers, in order by number of books.
pubs = Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5]
pubs[0].num_books
# 1323