django-orm.md

Django ORM examples

Models

class Author(models.Model):
    name = models.CharField(max_length=255)
    email = models.EmailField()
    age = models.IntegerField()

    def __str__(self):
        return self.name


class Article(models.Model):
    title = models.CharField(max_length=120)
    description = models.TextField()
    body = models.TextField()
    author = models.ForeignKey('Author',
                                related_name='articles',
                                on_delete=models.CASCADE)

    def __str__(self):
        return self.title

---

Get SQL query

a = Article.objects.all()

print(str(a.query))

---

ORM examples

---

a = Article.objects.all()

# SQL Query
'SELECT "article_author"."id", "article_author"."name", "article_author"."email", "article_author"."age" 
FROM "article_author"'

# Greater than
a = Author.objects.filter(age__gt=20)

# And condition
a = Author.objects.filter(age__gt=20, name='Rupesh')

# OR condition

from django.db.models import Q
a = Author.objects.filter(Q(age__gt=20)| Q(name='Rupesh'))

# AND and OR together
a = Author.objects.filter(Q(age__gt=20)| Q(name='Rupesh'), email='rupesh@gmail.com')

# NOT query
from django.db.models import Q

a = Author.objects.filter(~Q(age__gt=20))
a = Author.objects.exclude(age__gt=20)

# Exists true or false. More efficient then count
Author.objects.filter(age=25).exists()

# INNER JOIN in django
a = Article.objects.select_related('author')

# Select only required columns. This return list of dicts
a = Article.objects.select_related('author').values('author__name', 'title')

# Return list of tuples
a = Article.objects.select_related('author').values_list('author__name', 'title')

#ONly and defer
a = Article.objects.select_related('author').defer('title', 'description')
a = Article.objects.only('title', 'description')

# Annotate. SELECT col as NewCOl
from django.db.models import F

a = Article.objects.select_related('author').annotate(a_name=F('author__name'), art_title=F('title')).values('a_name', 'art_title')

# Select constant columns
a = Article.objects.select_related('author').annotate(a_name=F('author__name'), art_title=F('title')).extra(select={'indentation':'0'}).values('indentation', 'a_name')

# Count
a = Author.objects.annotate(name_count=Count('name'))

 a = Author.objects.all().aggregate(Avg('age'))

a = Author.objects.all().aggregate(Avg('age'), Max('age'), Min('age'))