#Django-Models  #Django 

1. Django ORM

The Django web framework includes a default object-relational mapping layer (ORM) that can be used to interact with various relational databases such as SQLite, PostgreSQL, and MySQL. It allows us to add, delete, modify, and query objects.

An object-relational mapper provides an object-oriented layer between relational databases and object-oriented programming languages without having to write SQL queries.

1.1 General/Basic Queries

# Shows all users
User.objects.all()

# shows first user
User.objects.first()

# shows last user
User.objects.last()

# Filter
Match.objects.filter(season='2016')

# Sliced
Entry.objects.all()[:10:2]

Find the query associated with a queryset

queryset = Event.objects.all()
str(queryset.query)

queryset.query.__str__()

ORDER BY

# Ascending
Match.objects.all().order_by('match_id')

# Descending
Match.objects.all().order_by('-match_id')

User.objects.all().order_by('is_active', '-last_login', 'first_name')

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)
  • Though this looks like three database hits,
  • In fact, it hits the database only once, at the last line (print(q))

Evaluation and Caching

  • Evaluation means actually hitting the database
  • Django QuerySet class has a _result_cache variable where it saves the query results in list. – Caching
# Cached
queryset = Entry.objects.all()    

# Evaluated
for each in queryset:
    print(each.headline)

1.2 Field lookups

i --> insensitive --> Case-insensitive

  • in
User.objects.filter(id__in=[1, 3, 4])

q1 = Blog.objects.filter(name__contains='Cheddar')
Entry.objects.filter(blog__in = q1)
  • exact and iexact
Language.objects.filter(name='Python')

Language.objects.filter(name__exact='Python')

Language.objects.filter(name__iexact='python')
Language.objects.filter(name__iexact='Python')
  • exclude
Language.objects.exclude(name='Python')
  • contains and icontains
Language.objects.all().filter(description__contains='keywords')
Language.objects.all().filter(description__icontains='KEYwords')
  • startswith and istartswith
Entry.objects.filter(headline__startswith='key')
Entry.objects.filter(headline__istartswith='KEy')
  • endswith and iendswith
Entry.objects.filter(headline__endswith='words')
Entry.objects.filter(headline__iendswith='WORds')
  • gt (Greater than), gte (Greater than or equal to)
Entry.objects.filter(id__gt=4)
  • lt (Less than), lte (Less than or equal to)
Language.objects.filter(bedrooms__lte=bedrooms)

1.3 Set Operations

Complex lookups with Q objects

from django.db.models import Q

OR queries in Django ORM

from django.db.models import Q
from customer.models import *

# Way1
# queryset_1 | queryset_2
q1 = Customer.objects.filter(f_name__startswith='tes')
q2 = Customer.objects.filter(f_name__startswith='amr')
print(q1.count(), q2.count(), ( q1 | q2 ).count())

# Way2
# filter(Q(<condition_1>)|Q(<condition_2>)
Customer.objects.filter(Q(f_name__startswith='tes')|Q(
    f_name__startswith='amr')).count()

AND queries in Django ORM

# Way1
# filter(<condition_1>, <condition_2>)
Customer.objects.filter(f_name__startswith='t', f_name__endswith='t'
      ).count()

# Way2
# queryset_1 & queryset_2
q1 = Customer.objects.filter(f_name__startswith='t')
q2 = Customer.objects.filter(f_name__endswith='t')
print(q1.count(), q2.count(), (q1 & q2).count())

# Way3
# filter(Q(<condition_1>) & Q(<condition_2>))
Customer.objects.filter(Q(f_name__startswith='t')&Q(f_name__endswith='t')
    ).count()

NOT query in Django queryset

# Way1
# exclude(<condition>)
Customer.objects.exclude(id__lt=5)

# Way2
# filter(~Q(<condition>))
Customer.objects.filter(~Q(id__lt=5))

Union of two queryset

  • Can be performed only with the queryset having same fields and the datatypes.
  • Using values_list union can be performed on different models with certain common fields.
# Same Model
q1 = Customer.objects.filter(id__lte=2)
q2 = Customer.objects.filter(id__gte=Customer.objects.last().id - 2)
q1.union(q2)


# Different Model
q1 = Customer.objects.all()[:2].values_list('f_name', 'mobile')
q2 = Customer.objects.all().order_by('-id')[:2].values_list(
    'f_name', 'mobile')
q1.union(q2)

1.4 F() Expression

from django.db.models import F

from django.db.models import F
from product.models import *

product_objs = Product.objects.all()

# Update 1 by 1
for product in product_objs:
    product.price += 1
    product.save()

# Update all at once
product_objs.update(price=F('price') + 1)

Single object update not recommended

  • The F() object persist after saving the model
product = Product.objects.get(id=3000)
product.price = 6501
product.save()

product.price = F('price') + 1
print(product.price)
# Issue1
# <CombinedExpression: F(price) + Value(1)>

product.save()
product.save()
product.save()
product.refresh_from_db()
print(product.price)
# Issue2 - higher value 
# 6504

# first_name==last_name
Customer.objects.filter(l_name=F("f_name")).values_list(
    'f_name', 'l_name')

1.5 Aggregation

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

# Total no of purchase
print(sum(list(Product.objects.all().values_list(
    'purchase_count', flat=True))))
print(Product.objects.all().aggregate(Sum('purchase_count')))

# Min purchase
print(Product.objects.all().order_by('purchase_count')[0].purchase_count)
print(Product.objects.all().aggregate(Min('purchase_count')))

# Max purchase
print(Product.objects.all().order_by('-purchase_count')[0].purchase_count)
print(Product.objects.all().aggregate(Max('purchase_count')))

# No of Product and ProductVariant
print(Product.objects.count())
print(ProductVariant.objects.count())

1.6 Django Annotations

  • Annotate allows us to add a pseudo field to our queryset …read more

Count, Sum

from django.db.models import Count, Sum

# No of variants per product
print(Product.objects.annotate(variants=Count('productvariant')
    ).values_list('id', 'variants'))

# No of stock per product
print(Product.objects.annotate(var_stock=Sum('productvariant__stock_count')
    ).order_by('-var_stock').values_list('var_stock', flat=True))

Find rows which have duplicate field values

# ROWS with Same Name
from django.db.models import Count

Customer.objects.values('f_name').annotate(ncount=Count('f_name')
    ).filter(ncount__gt=1)
    
# If you need to fill all the records, you can do
names = [item['f_name'] for item in duplicates]
Customer.objects.filter(f_name__in=names).values_list('id', flat=True)

Min, Max

from django.db.models import Min, Max

# Variant with highest stock
Product.objects.annotate(var_max_stock=Max('productvariant__stock_count')
    ).order_by('-var_max_stock').values_list('var_max_stock', 'id')

# Variant with lowest stock
Product.objects.annotate(var_min_stock=Min('productvariant__stock_count')
    ).order_by('-var_min_stock').values_list('var_min_stock', 'id')

annotate + filter

from django.db.models import Min, Sum, Q

# Get stock-count for active Variants
pd_objs = Product.objects.filter(can_stock=True).annotate(
    variant_min_stock = Min('productvariant__stock_count', filter=Q(
    productvariant__is_active=True)))
print(pd_objs[0].__dict__)

# Get Variant with 
product_objs = Product.objects.filter(can_stock=True).annotate(
    variant_min_stock = Min('productvariant__stock_count', filter=Q(
    productvariant__is_active=True)), stock_count=Sum(
    'productvariant__stock_count')).order_by(
    '-stock_count').exclude(variant_min_stock=None)

Concat

  • If we use @property for full_name - this value will be loaded even if it is not required
  • Concat has better performance
from django.db.models.functions import Concat
from django.db.models import Value

Customer.objects.annotate(full_name=Concat(
    'f_name', Value(' '), 'l_name'))[0].full_name

Coalesce

  • Empty string is also considered as value
  • Works on CharField
from django.db.models.functions import Coalesce

# Display mobile, if None or '' then display f_name
print(Customer.objects.annotate(ph_or_fname=Coalesce(
  'mobile', 'f_name')).values_list('ph_or_fname', flat=True))

# Empty string is also considered as value
print(Customer.objects.filter(mobile='').annotate(ph_or_fname=Coalesce(
    'mobile', 'f_name')).values_list('ph_or_fname', flat=True))

NullIf

  • Ignore Empty string, using NullIf
from django.db.models.functions import Concat, Coalesce, NullIf
from django.db.models import Value

# Display mobile, if Empty/None then display f_name
print(Customer.objects.filter(mobile='').annotate(ph_or_fname=Coalesce(
  NullIf('mobile', Value('')), 'f_name')).values_list(
  'ph_or_fname', flat=True))

print(Customer.objects.annotate(ph_or_fname=Coalesce(NullIf(
    'mobile', Value('')), 'f_name')).filter(ph_or_fname=''
    ).values_list('ph_or_fname', flat=True))

When, Case

Reference