susmithagudapati

from django.db.models import Avg, Count

# first annotates each product with number of attributes,
# and then aggregates the average number of authors using the annotated attribute

Product.objects.annotate(num_attributes=Count('attributes')).aggregate(Avg('num_attributes'))
{'num_authors__avg': 3.46}

susmithagudapati

# usage of annotation

# annotates number of attributes to each product.
products = Product.objects.annotate(number_of_attributes=Count('attributes'))
products[0].number_of_attributes
4

# the other way to define
products = Product.objects.annotate(Count('attributes'))
products[0].attributes__count

susmithagudapati

# basic usage of aggregation

# Max price of all products.
from django.db.models import Max
Product.objects.all().aggregate(Max('price'))
{'price__max': 70.15}

# Average view count across all products.
from django.db.models import Avg
Product.objects.all().aggregate(Avg('view_count'))

susmithagudapati

# retrieving products with all fields
%timeit Product.objects.all()                                                                                                                                                                                                                        
6.08 µs ± 97.2 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)

# loading products with only upc column
%timeit Product.objects.only('upc')                                                                                                                                                                                                                  
15.4 µs ± 117 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)


# more about 'only()'

susmithagudapati

# retrieving products with all fields
%timeit Product.objects.all()                                                                                                                                                                                                                        
6.08 µs ± 97.2 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)


# fetching products after deferring name and category
%timeit Product.objects.defer('name', 'category')                                                                                                                                                                                                   
14.4 µs ± 103 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)

susmithagudapati

# executed by Python
%timeit if Product.objects.all(): print("valid")                                                                                                                                                                                                                    
4.19 ms ± 118 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)


# executed in the database
%timeit Product.objects.exists()                                                                                                                                                                                                                     
226 µs ± 4.73 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

susmithagudapati

# slower because it loads all products into Python memory to execute 'len()'
%timeit len(Product.objects.all())                                                                                                                                                                                                                   
3.47 ms ± 36 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)


# faster because an SQL expression executes this
%timeit Product.objects.count()                                                                                                                                                                                                                      
267 µs ± 7.51 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

susmithagudapati

# takes more time because it loads the whole model object into Python memory
%timeit upcs = [product.upc for product in Product.objects.all()]                                                                                                                                                                                    
3.91 ms ± 131 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

# faster than the above code because an SQL expression executes this
%timeit Product.objects.values_list('upc', flat=True)                                                                                                                                                                                                           
32.3 µs ± 1.12 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)

susmithagudapati

# these 2 queries are faster because upc & id are unique and indexed.
product = Product.objects.get(upc='PXO01')
product = Product.objects.get(id=33)

# this is much slower than above queries because name isn't indexed.
product = Product.objects.get(name='Neon Shoes')

# this query can have multiple matches which hinders query performance.
product = Product.objects.get(name__startswith='Neon')

susmithagudapati

# Python's implementation
products = Product.objects.filter(category__name='shoes')
for product in products:
  product.stock += 5
  product.save()
  
  
# SQL incrementing using F() method.
Product.objects.filter(category__name='shoes').update(stock=F('stock') + 5)