I Didn't Know QuerySets Could Do That

Wait But Why

QuerySets

User.objects.all()

Article.objects.filter(...)

Reference Models


class Product(Model):
    name # str
    price # float
    seller # User
        

class Order(Model):
    buyer # User
    total # float
    status # str
    ordered_at # datetime
        

class Item(Model):
    order # Order
    product # Product
    quantity # int
    unit_price # Product.price
        

Basics

  • all
  • filter
  • exclude
  • order_by
  • reverse
  • distinct
  • annotate
  • get
  • first
  • last
  • latest
  • earliest
  • exists
  • count
  • aggregate

products = products.annotate(Count('item'))
products.first().item__count # 24

grand_total = orders.aggregate(Sum('total'))
grand_total['total__sum'] # 999.99
    

Unbasics

  • select_related
  • prefetch_related
  • defer
  • only
  • using
  • values
  • values_list
  • in_bulk
  • bulk_create

product = Product.objects.get(...)
print(product.seller.id) # extra DB query
        

product = Product.objects.select_related('seller').get(...)
print(product.seller.id) # no extra DB query
        

product = Product.objects.only('name', 'price').get(...)
# faster when accessing 'name' and 'price'
        

product = Product.objects.defer('expensive_field').get(...)
# faster when accessing all fields except 'expensive_field'
        

products = Product.objects.in_bulk([1, 2])
# {1: <Product: DjangoCon Shirt>,
#  2: <Product: DjangoCon Mug>}
        

# filter items by month ordered
items = Item.objects.filter(order__ordered_at__month=...)
# list product IDs
product_ids = items.values_list('product_id', flat=True)
                   .distinct()
# query for products
products = Product.objects.in_bulk(product_ids)
        

Conjunction Dysfunction


orders = Order.objects.filter(status='shipped',
                              ordered_at__lt=yesterday)
        

SELECT ... WHERE status = 'shipped'
AND ordered_at < '2016-7-20'
        

Example: OR Queries

def search_users(query):

search_users('kelly')
# Charlie Kelly charliek@gmail.com
# Kelly Jones jones22@hotmail.co.uk
# Katie Elly kelly@python.org
        

fname = User.objects.filter(first_name__icontains='kelly')
lname = User.objects.filter(last_name__icontains='kelly')
email = User.objects.filter(email__icontains='kelly')
users = list(fname) + list(lname) + list(email)
        

Please No.

Q!


users = User.objects.filter(
            Q(first_name__icontains='kelly') |
            Q(last_name__icontains='kelly') |
            Q(email__icontains='kelly')
        ).order_by('last_name')
        

SELECT ... WHERE (first_name LIKE %kelly% OR
                  last_name LIKE %kelly% OR
                  email LIKE %kelly%)
ORDER BY last_name ASC
        


.filter(
    Q(first_name='John') |
    ~Q(middle_name='Jacob') &
    Q(last_name='Jingle Heimer Schmidt')
)
        

Example: Query Arithmetic

def item_total(items):

items = Item.objects.filter(...)
item_total(items)
# 420.42
        

total = items.aggregate(Sum('unit_price'))
             .get('unit_price__sum', 0)
        

Easy... peasy?


class Item(Model):
    order # Order
    product # Product
    quantity # int
    unit_price # Product.price
        
    def total_price(self):
        return (self.unit_price * self.quantity)

total = sum([i.total_price() for i in items])
        

F!


item_sum = Sum(F('unit_price') * F('quantity'))
total = items.aggregate(amount=item_sum)
             .get('amount', 0)
        

Example: Inline References

def increment_purchases(products):

class Product(Model):
    ...
    purchases # int
        

for product in products:
    product.purchases += 1
    product.save()
        

products.update(purchases=F('purchases') + 1)
        

Query Expressions

  • Avg
  • Count
  • Max
  • Min
  • StdDev
  • Sum
  • Variance
  • Coalesce
  • Concat
  • Greatest
  • Least
  • Length
  • Lower
  • Now
  • Substr
  • Upper

# lowercase product names
products.annotate(name_lower=Func(F('name'),
                                  function='LOWER'))

# split user email domain
users.annotate(domain=Func(F('email'), Value('@'), Value(2),
                           function='SPLIT_PART'))
        
  • F
  • Aggregate
  • Func
  • Value
  • ExpressionWrapper
  • Conditional
  • as_sql
  • as_<vendorname>
  • get_lookup
  • get_transform
  • output_field

Example: Order Totals (Redux)


item_sum = Sum(F('unit_price') * F('quantity'))
total = items.aggregate(amount=item_sum)
             .get('amount', 0)
        

Mea Culpa.


query_expression = ExpressionWrapper(
                       Sum(F('unit_price') * F('quantity')),
                       output_field=FloatField()
                   )
total = items.aggregate(amount=query_expression)
             .get('amount', 0)
        

We Must Go Deeper!

A Note On Raw SQL

IF YOU'RE WRITING RAW SQL, YOU SHOULD PROBABLY RETHINK WHAT YOU'RE ABOUT TO DO

</PSA>

extra


Order.objects.extra(
    select={'is_recent': "ordered_at > '2016-07-01'"}
)
        

SELECT store_order.*, (ordered_at > '2016-07-01')
AS is_recent
FROM store_order
        
  • select
  • where
  • tables
  • order_by
  • select_params
  • params

raw


Order.objects.raw("""SELECT store_order.*,
                     (ordered_at > '2016-07-18')
                     AS ordered_yesterday
                     FROM store_order""")
    

Let's Recap

  • Basic Methods
    • Use annotate/aggregate where possible
  • Advanced Methods
    • select_related/prefetch_related for reducing JOINs
    • only/defer to partially fetch models
  • Q objects
    • Encapsulated query constraints
  • F objects
    • Implicit field/column references
  • Database Functions
    • Sum, Avg, Count, etc
    • Concat, Greater, Lower, etc
  • Query Expressions
    • See the docs on writing your own
  • Raw SQL (ಠ_ಠ)
    • extra
    • raw

Charlie Guo

@charlierguo

Unscalable

unscalablebook.com

</shameless_plug>

Unscalable, The Book

Thanks!