❮ Django Views Fbv Cbv Django Template ❯

Django ORM – Multi-Table Example

The relationships between tables can be categorized into the following three types:

One-to-One: A person corresponds to one ID number, with unique fields set.
One-to-Many: One family has multiple people, typically implemented through foreign keys.
Many-to-Many: A student has multiple courses, and a course has many students, usually implemented through a third table for association.

Creating Models

Next, let's look at a multi-table example.

Example

class Book(models.Model):
    title = models.CharField(max_length=32)
    price = models.DecimalField(max_digits=5, decimal_places=2)
    pub_date = models.DateField()
    publish = models.ForeignKey("Publish", on_delete=models.CASCADE)
    authors = models.ManyToManyField("Author")

class Publish(models.Model):
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=64)
    email = models.EmailField()

class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.SmallIntegerField()
    au_detail = models.OneToOneField("AuthorDetail", on_delete=models.CASCADE)

class AuthorDetail(models.Model):
    gender_choices = (
        (0, "Female"),
        (1, "Male"),
        (2, "Confidential"),
    )
    gender = models.SmallIntegerField(choices=gender_choices)
    tel = models.CharField(max_length=32)
    addr = models.CharField(max_length=64)
    birthday = models.DateField()

Notes:

1. The EmailField data type is in email format, underlyingly inherited from CharField, encapsulated, equivalent to varchar in MySQL.
1. Django 1.1 versions do not require cascading delete: on_delete=models.CASCADE, but Django 2.2 does.
1. Cascading updates are generally not needed.
1. Foreign keys are set in the "many" of one-to-many relationships: models.ForeignKey("RelatedClassName", on_delete=models.CASCADE).
1. OneToOneField = ForeignKey(...，unique=True) sets a one-to-one relationship.
1. If a model class has a foreign key, data creation should start with the foreign key-related model class data first; otherwise, the foreign key-related model class data will not be found when creating data that includes the foreign key.

Table Structure

Book Table: title, price, pub_date, publish (foreign key, many-to-one), authors (many-to-many)

Publish Table: name, city, email

Author Table: name, age, au_detail (one-to-one)

AuthorDetail Table: gender, tel, addr, birthday

Below is the table association explanation:

Inserting Data

We execute the following SQL insert operations in MySQL:

insert into app01_publish(name,city,email) values ("Huashan Publishing", "Huashan", "[email protected]"), ("Mingjiao Publishing", "Heimuya", "[email protected]")

# Insert multiple data into the authordetail table first
```sql
insert into app01_authordetail(gender, tel, addr, birthday) values (1, 13432335433, "Mount Hua", "1994-5-23"), (1, 13943454554, "Hei Mu Ya", "1961-8-13"), (0, 13878934322, "Hei Mu Ya", "1996-5-20");

# Then insert data into author so that author can find authordetail
insert into app01_author(name, age, au_detail_id) values ("Linghu Chong", 25, 1), ("Ren Wo Xing", 58, 2), ("Ren Ying Ying", 23, 3);

pk = chong.pk
# Get the book object
book = models.Book.objects.filter(title="冲灵剑法").first()
# Add the author object's id to the book's authors attribute using the add method
book.authors.add(pk)

Related Manager (Object Invocation)

Prerequisites:

Many-to-Many (both sides have a related manager)
One-to-Many (only the side with many has a related manager, i.e., reverse only)

Syntax Format:

Forward: attribute name
Reverse: lowercase class name followed by _set

Note: One-to-Many can only be reversed.

Common Methods:

add(): Used for many-to-many relationships, adds the specified model object to the set of related objects (relationship table).

Note: In one-to-many (foreign key), add() can only take objects (QuerySet data type), not ids ([id list]).

Usage of *[ ]:

book_obj = models.Book.objects.get(id=10)
author_list = models.Author.objects.filter(id__gt=2)
book_obj.authors.add(*author_list)  # Adds author objects with id greater than 2 to the book's author collection
# Alternative method: passing object ids
book_obj.authors.add(*[1,3])  # Adds author objects with id=1 and id=3 to the book's author collection
return HttpResponse("ok")

Reverse: lowercase_table_name_set

ying = models.Author.objects.filter(name="任盈盈").first()
book = models.Book.objects.filter(title="冲灵剑法").first()
ying.book_set.add(book)
return HttpResponse("ok")

create(): Creates a new object and adds it to the set of related objects simultaneously.

Returns the newly created object.

pub = models.Publish.objects.filter(name="明教出版社").first()
wo = models.Author.objects.filter(name="任我行").first()
book = wo.book_set.create(title="吸星大法", price=300, pub_date="1999-9-19", publish=pub)
print(book, type(book))
return HttpResponse("ok")

remove(): Removes the specified model object from the set of related objects.

For ForeignKey objects, this method only exists if null=True (can be empty), and it returns no value.

Example:

author_obj = models.Author.objects.get(id=1)
book_obj = models.Book.objects.get(id=11)
author_obj.book_set.remove(book_obj)
return HttpResponse("ok")

clear(): Removes all objects from the set of related objects, deleting the association but not the objects.

For ForeignKey objects, this method only exists if null=True (can be empty).

Returns no value.

# Clear all authors associated with 独孤九剑
book = models.Book.objects.filter(title="tutorialpro.org").first()
book.authors.clear()

ORM Queries

Object-based cross-table queries.

Forward: attribute name
Reverse: lowercase class name_set

One-to-Many

Query the city where the publisher of the book with primary key 1 is located (forward).

Example:

book = models.Book.objects.filter(pk=10).first()
res = book.publish.city
print(res, type(res))
return HttpResponse("ok")

Query the names of the books published by 明教出版社 (reverse).

Reverse: object.lowercase_class_name_set(pub.book_set) can jump to the related table (book table).

pub.book_set.all(): Retrieves all book objects in the book table, stored in a QuerySet, and iterates through them to get individual book objects.

Example:

pub = models.Publish.objects.filter(name="明教出版社").first()
res = pub.book_set.all()
for i in res:

print(i.title)
return HttpResponse("ok")

One-to-One

Query Linghu Chong's phone number (forward).

Forward: object.attribute (author.au_detail) can jump to the associated table (Author Detail Table).

Example

author = models.Author.objects.filter(name="Linghu Chong").first()
res = author.au_detail.tel
print(res, type(res))
return HttpResponse("ok")

Query all authors living in Heimuya (reverse).

In a one-to-one reverse relationship, use object.lowercase_class_name without adding _set.

Reverse: object.lowercase_class_name (addr.author) can jump to the associated table (Author Table).

Example

addr = models.AuthorDetail.objects.filter(addr="Heimuya").first()
res = addr.author.name
print(res, type(res))
return HttpResponse("ok")

Many-to-Many

Query the names and phone numbers of all authors of "tutorialpro.org" (forward).

Forward: object.attribute (book.authors) can jump to the associated table (Author Table).

Since the Author Table does not have the author's phone number, jump again using object.attribute (i.au_detail) to the associated table (Author Detail Table).

Example

book = models.Book.objects.filter(title="tutorialpro.org").first()
res = book.authors.all()
for i in res:
    print(i.name, i.au_detail.tel)
return HttpResponse("ok")

Query all book titles authored by Ren Wo Xing (reverse).

Example

author = models.Author.objects.filter(name="Ren Wo Xing").first()
res = author.book_set.all()
for i in res:
    print(i.title)
return HttpResponse("ok")

Cross-Table Query Using Double Underscores

Forward: attribute_name__cross_table_attribute_name

Reverse: lowercase_class_name__cross_table_attribute_name

One-to-Many

Query all book titles and prices published by "tutorialpro".

Example

res = models.Book.objects.filter(publish__name="tutorialpro").values_list("title", "price")

Reverse: Cross-table data retrieval using lowercase_class_name__cross_table_attribute_name (book__title, book__price).

Example

res = models.Publish.objects.filter(name="tutorialpro").values_list("book__title","book__price")
return HttpResponse("ok")

Many-to-Many

Query all book titles authored by Ren Wo Xing.

Forward: Cross-table data retrieval using attribute_name__cross_table_attribute_name (authors__name).

res = models.Book.objects.filter(authors__name="Ren Wo Xing").values_list("title")

Reverse: Cross-table data retrieval using lowercase_class_name__cross_table_attribute_name (book__title).

res = models.Author.objects.filter(name="Ren Wo Xing").values_list("book__title")

One-to-One

Query Ren Wo Xing's phone number.

Forward: Cross-table data retrieval using attribute_name__cross_table_attribute_name (au_detail__tel).

res = models.Author.objects.filter(name="Ren Wo Xing").values_list("au_detail__tel")

Reverse: Cross-table data retrieval using lowercase_class_name__cross_table_attribute_name (author__name).

res = models.AuthorDetail.objects.filter(author__name="Ren Wo Xing").values_list("tel")

❮ Django Views Fbv Cbv Django Template ❯