Django Orm (QuerySet API)

Defining Models

from django.db import models


class BlogPost(models.Model):
  title = models.CharField(max_length=200)
  content = models.TextField()
  pub_date = models.DateTimeField('date published')
  category= models.CharField(max_length=200)
  def __str__(self):
    return self.title

Basic Database Operation

-all()

Querying Data

from .models import BlogPost
all_posts = BlogPost.objects.all()

– get():

get(): If you expect a single result, you can use the .get() method to retrieve it. Be cautious; it raises an exception if there’s no matching record or multiple records.

from .models import BlogPost
post = BlogPost.objects.get(title="A Specific Post")

– first() and last():

– first() and last(): To retrieve the first or last record in a queryset, you can use the .first() and .last() methods.

from .models import BlogPost
first_post = BlogPost.objects.first()
last_post = BlogPost.objects.last()

– filter():

from .models import BlogPost
from datetime import date
specific_post = BlogPost.objects.filter(category='Tech')

multiple filter

specific_post = BlogPost.objects.filter(date(2023,1,1),category='Tech')

– exclude():

– exclude(): The .exclude() method allows you to exclude records that match certain conditions.

from myapp.models import BlogPost
nontech_post= BlogPost.objects.exclude(category='Tech')

Chaining Queries

One of the strengths of Django ORM is the ability to chain multiple queries together. This allows you to construct complex queries step by step. For example, to get recent posts that are not in the “Tech” category:

from .models import BlogPost
from datetime import date
queryset = BlogPost.objects.filter(pub_date__gt=date(2023, 1, 1)).exclude(category="Tech")

Creating, Updating, and Deleting Records

Create Comment

To create a new record, instantiate a model object and call .save():

new_post = BlogPost(title="New Post", content="This is a new post.", pub_date=date.today())
new_post.save()

UpdateComment

Updating a record is straightforward. Retrieve the record, modify its attributes, and call .save() again:

post_to_update = BlogPost.objects.get(title="Old Post")
post_to_update.content = "This post has been updated."
post_to_update.save()

DeleteComment

Deleting a record is as simple as calling the .delete() method:

post_to_delete = BlogPost.objects.get(title="Post to Delete")
post_to_delete.delete()

Aggregation

from django.db.models import Sum
from myapp.models import Order

# Calculate the total price of all orders
total_price = Order.objects.aggregate(total_price=Sum('total_price'))
print(total_price)

Annotation

Annotation: Annotation is used to add extra information to each record in a queryset. You can add calculated fields to the records in the queryset without changing the underlying data in the database.

Let’s say we want to annotate each Order object with the number of items in that order:

from django.db.models import F
from myapp.models import Order, OrderItem

# Annotate each order with the count of items
orders_with_item_count = Order.objects.annotate(item_count=Count('orderitem'))

for order in orders_with_item_count:
    print(f"Order #{order.id} has {order.item_count} items.")

In this example, we’re using the annotate method to add an item_count field to each Order object. This field is calculated based on the related OrderItem records associated with each order.

Model Relationships and Related Names

Page actions

Understanding how to define and manage one-to-one, many-to-one, and many-to-many relationships between your models, as well as the concept of related_name and the default related name, is crucial for building complex and data-rich applications.

One-to-One Relationships

Comment

One-to-one relationships are often used when two models share a unique relationship. For example, let’s say we have a Profile model associated with a User model. Each user should have exactly one profile, and each profile belongs to a single user. In Django, you can define a one-to-one relationship using the OneToOneField:

from django.db import models
from django.contrib.auth.models import User


class Profile(models.Model):
  user = models.OneToOneField(User, on_delete=models.CASCADE, related_name="profile")
  bio = models.TextField()
  website = models.URLField()

Many-to-One Relationships

Create the Post and Comment models:


from django.db import models

class Post(models.Model):
    title = models.CharField(max_length=100)
    content = models.TextField()

class Comment(models.Model):
    post = models.ForeignKey(Post, on_delete=models.CASCADE)
    text = models.TextField()
    date_created = models.DateTimeField(auto_now_add=True)

In this example:Comment

We have two models: Post and Comment. Each Post can have multiple Comment objects associated with it, creating a Many-to-One relationship. The Comment model has a foreign key field post that establishes the relationship to the Post model. The on_delete=models.CASCADE argument means that if a Post is deleted, all its associated comments will be deleted as well. We also have a text field to store the content of the comment and a date_created field to store the creation date.Comment

Create and retrieve Post and Comment objects:

# Create a new post
post = Post.objects.create(title="Sample Post", content="This is the content of the post.")

# Create comments related to the post
comment1 = Comment.objects.create(post=post, text="First comment on the post.")
comment2 = Comment.objects.create(post=post, text="Second comment on the post.")

# Retrieve comments for a specific post
comments_for_post = Comment.objects.filter(post=post)

# Access the related post for a comment
for comment in comments_for_post:
    print(f"Comment: {comment.text}")
    print(f"Posted on: {comment.date_created}")
    print(f"Related Post: {comment.post.title}\n")

We first create a Post and two related Comment objects. Each comment is associated with the post it relates to. We then retrieve all comments related to a specific post using Comment.objects.filter(post=post). To access the related Post for each comment, we use the comment.post attribute, which gives us access to the associated Post object. This example demonstrates a Many-to-One relationship in Django between the Post and Comment models. It allows you to associate multiple comments with a single post and easily access them through the foreign key relationship.

Many-to-Many Relationships

Many-to-many relationships are used when multiple instances of one model can be related to multiple instances of another model. Consider a scenario where we have a Tag model associated with a Post model. A post can have multiple tags, and a tag can be associated with multiple posts. In Django, you can define a many-to-many relationship using the ManyToManyField:

from django.db import models


class Tag(models.Model):
  name = models.CharField(max_length=50)


class Post(models.Model):
  title = models.CharField(max_length=200)
  content = models.TextField()
  tags = models.ManyToManyField(Tag, related_name="posts")

In this example, the Post model includes a tags field, which is a ManyToManyField that establishes a many-to-many relationship with the Tag model. The related_name=”posts” option allows you to access the posts related to a tag easily.

# Import your models
from yourapp.models import Post

# Assuming you have a post with the ID 1
post = Post.objects.get(id=1)

# Access the tags associated with the post using the related_name "posts"
tags = post.tags.all()

# Loop through and print the names of the tags
for tag in tags:
    print(tag.name)

Reverse Relationships

In Django, you can define reverse relationships to access related objects from the reverse side of a ForeignKey or ManyToManyField relationship. Let’s create a simple example using the Author and Book models to demonstrate reverse relationships.Comment

1. Create the Author and Book models:

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

In this example:Comment

We have two models: Author and Book.Comment
Each Book has a foreign key relationship with an Author, meaning each book is authored by a specific author.Comment

Comment

2. Create and retrieve Author and Book objects:

# Create authors
author1 = Author.objects.create(name="J.K. Rowling")
author2 = Author.objects.create(name="George Orwell")

# Create books by associating them with authors
book1 = Book.objects.create(title="Harry Potter and the Sorcerer's Stone", author=author1)
book2 = Book.objects.create(title="1984", author=author2)
book3 = Book.objects.create(title="Harry Potter and the Chamber of Secrets", author=author1)

# Access books by a specific author using the reverse relationship
books_by_rowling = author1.book_set.all()

# Print the titles of books by J.K. Rowling
for book in books_by_rowling:
    print(f"Book by J.K. Rowling: {book.title}")

In this code:Comment

We create two authors, J.K. Rowling and George Orwell, and three books associated with these authors.Comment
To access the books written by a specific author (in this case, J.K. Rowling), we use the book_set attribute, which is automatically generated by Django for the reverse relationship from Author to Book.Comment
We then loop through the books written by J.K. Rowling and print their titles.Comment

Comment

The book_set attribute allows you to access the related Book objects for a specific Author. You can also define a related_name on the ForeignKey to provide a custom name for the reverse relationship if you prefer not to use the default modelname_set.

Understanding related_name

আমরা যদি related_name এট্রিবিউট ব্যবহার না করি তাহলে ডিফল্ট modelname_set ব্যবহার করতে হবে আর related_name এট্রিবিউট ব্যবহার করলে আমরা সরাসরি modelname.field এ একসেস করতে পারবো।Comment

By specifying related_name, you can replace the default reverse relation name, which is typically the lowercase name of the related model followed by “_set”. For example, without related_name, you would access related comments like this: post.comment_set.all().

Advanced Querying with Django ORM

Chaining Queries

Chaining Filters and Ordering

from myapp.models import Product

# Chaining filters
queryset = Product.objects.filter(category="Electronics").filter(price__lte=500)

# Chaining ordering
queryset = queryset.order_by('-price')

F() Expressions

F() Expressions ডেটাবেজ থেকে ভ্যালু প্যারামিটার হিসাবে নিয়ে ডেটাবেজের সাথে বিভিন্ন অপারেশন করতে সাহায্য করে। মূলত আপডেট এবং ফিল্টার করার জন্য ব্যবহার করা হয়।

from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=100)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    quantity = models.PositiveIntegerField()
    discounted_price = models.DecimalField(max_digits=10, decimal_places=2)

আপডেট করার জন্য Comment

সমস্ত price কে দুই দ্বারা গুন করলাম।

from django.db.models import F

# Double the price of all products
Product.objects.update(price=F('price') * 2)

ফিল্টার করার জন্য

from django.db.models import F

# Retrieve products where the regular price is greater than the discounted price
products = Product.objects.filter(price__gt=F('discounted_price'))

Q() Objects

AND, OR, NOT এই জাতীয় কমপ্লেক্স কোয়েরি করার জন্য Q() ব্যবহার করা হয়।

from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.CharField(max_length=50)
    published_year = models.PositiveIntegerField()
    genre = models.CharField(max_length=50)

OR Example

from django.db.models import Q

# Retrieve books that match the criteria
books = Book.objects.filter(Q(published_year__gte=2000) | Q(genre='Science Fiction'))

And Example

# Retrieve books that match both conditions using AND
books = Book.objects.filter(Q(published_year__gte=2000) & Q(genre='Science Fiction'))

NOT Example

from django.db.models import Q

# Retrieve books that do not match the condition using NOT
books = Book.objects.filter(~Q(published_year__gte=2000))

Dynamic Search

Page actions

অনেক সময় ব্যবহারকারীর কীওয়ার্ড এর উপর ভিত্তি করে ডাইনামিক সার্চ রেজাল্ট রিটার্ন করতে হয় তখন Q() ব্যবহার করে ডাইনামিক সার্চ রেজাল্ট প্রদান করতে হয়

Raw SQL Queries

Raw SQL

Retrieving Data

from myapp.models import MyModel
from django.db import connection

# Execute a raw SQL query to retrieve data
with connection.cursor() as cursor:
 cursor.execute("SELECT * FROM myapp_mymodel WHERE price < %s", [100])
 results = cursor.fetchall()

Updating Data

from myapp.models import Product
from django.db import connection

# Execute a raw SQL query to update data
with connection.cursor() as cursor:
 cursor.execute("UPDATE myapp_product SET price = price * 1.10 WHERE category = %s", ["Electronics"])

Using Parameterized Queries

from myapp.models import Product
from django.db import connection

# Execute a parameterized raw SQL query
category = "Electronics"
with connection.cursor() as cursor:
 cursor.execute("SELECT * FROM myapp_product WHERE category = %s", [category])
 results = cursor.fetchall()

Mapping Raw SQL to Model ObjectsComment

আমাদের ডেটাবেজ এ myapp_person নামে টেবিল আছে আমরা ওই টেবিল থেকে ডেটা আমাদের app এ Person নামে মডেল এ ম্যাপিং করবো

from myapp.models import Person

# Execute a raw SQL query to retrieve data and map it to the Person model
for p in Person.objects.raw("SELECT * FROM myapp_person"):
 print(p)

Performance Optimization with Django ORM

Select Related

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

মনে করি আমার ডেটাবেজ এ নিচের মত ডেটা আছে

Author Data:
--------------
ID: 1

Name: John Smith

ID: 2

Name: Jane Doe



Book Data:
--------------
ID: 1

Title: "Book 1"

Author: 1 (John Smith)

ID: 2

Title: "Book 2"

Author: 1 (John Smith)

ID: 3

Title: "Book 3"

Author: 2 (Jane Doe)

এবার একটি নরমাল কোয়েরি চালিয়ে book মডেল থেকে author এর নাম বের করি।

books = Book.objects.all()

for book in books:
    author = book.author  # Retrieves the author for each book (causing an additional query)
    print(f"Book: {book.title}, Author: {author.name}")

উপরের কোয়েরি টি চালানোর ফলে প্রথমে সব বুক অবজেক্ট নিয়ে আসবে যার মধ্যে author নাম থাকবে না পরে আরেকটি কোয়েরি চালিয়ে (ফর লুপে চালানো হয়েছে ) ডেটা আনতে হয়েছে তার পর ডেটা এসেছে।

<QuerySet [
    <Book: Book 1>,
    <Book: Book 2>,
    <Book: Book 3>
]>

এবার releated() কোয়েরি চালাই :

books = Book.objects.select_related('author')

for book in books:
    author = book.author  # No additional query is made because of select_related()
    print(f"Book: {book.title}, Author: {author.name}")

উপরের কোয়েরি টি চালানোর ফলে বুক অবজেক্ট নিয়ে আসবে যার মধ্যে author নাম থাকবে যার কারণে ফর লুপের মধ্যে নতুন কোন কোয়েরি লাগবে না সরাসরি ডেটা প্রদান করবে।

<QuerySet [
    <Book: A Tale of Two Cities (Author: John Smith)>,
    <Book: To Kill a Mockingbird (Author: John Smith)>,
    <Book: The Great Gatsby (Author: [Author not loaded])>
]>

Django

Basic Database Operation

Model Relationships and Related Names

Advanced Querying with Django ORM

Dynamic Search

Raw SQL Queries

Performance Optimization with Django ORM

How can we help?