1, Create model

Next, we design the corresponding relationship between each table through the book management system.

Through the relationship above, we can define our model class.

from django.db import models


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, "secrecy"),
    )
    gender = models.SmallIntegerField(choices=gender_choices)
    tel = models.CharField(max_length=32)
    addr = models.CharField(max_length=64)
    birthday = models.DateField()

Note that don't forget the steps of django using MySQL database. Only after step-by-step implementation can the database be operated through orm.

2, Operation table record

1. Add some simple data

1. publish table:

insert into app01_publish(name,city,email) 
values
("Huashan Publishing House", "Huashan Mountain", "hs@163.com"), 
("Mingjiao press", "Blackwood cliff", "mj@163.com")

2. author table:

insert into app01_author(name,age,au_detail_id) 
values
("linghu chong",25,1),
("ren woxing",58,2),
("Ren yingying",23,3) 

3. authordatail table:

insert into app01_authordetail(gender,tel,addr,birthday) 
values
(1,13432335433,"Huashan Mountain","1994-5-23"),
(1,13943454554,"Blackwood cliff","1961-8-13"),
(0,13878934322,"Blackwood cliff","1996-5-20") 

2. One to many

# The form of object transmitted by mode I
pub_obj = models.Publish.objects.get(pk=1)
book = models.Book.objects.create(title="Dugu nine Swords", price=180, pub_date="2018-10-23", publish=pub_obj)

# Mode 2 transfer object id Form of
book = models.Book.objects.create(title="Dugu nine Swords", price=180, pub_date="2018-10-23", publish_id=1)

Core: understand the difference between book.publish and book.publish "ID?

3. Many to many

# The form of object transmitted by mode I
book = models.Book.objects.filter(title="Dugu nine Swords").first()
ling = models.Author.objects.filter(name="linghu chong").first()
ying = models.Author.objects.filter(name="Ren yingying").first()
book.authors.add(ling, ying)

# Mode 2 transfer object id Form of
book = models.Book.objects.filter(title="Dugu nine Swords").first()
ling = models.Author.objects.filter(name='linghu chong').first()
ying = models.Author.objects.filter(name='Ren yingying').first()
book.authors.add(ling.pk, ying.pk)

Core: what is book.authors.all()?

Many to many other common API s:

book_obj.authors.remove()      # Remove a specific object from the associated object collection. ======   book_obj.authors.remove(*[])
book_obj.authors.clear()       #Empty the collection of associated objects
book_obj.authors.set()         #Clear before setting

　　more

4. Cross table query based on object

1. One to many query (Publish and Book)

Forward query by field publish

# Query the city where the publishing house of the book with primary key 1 is located
book_obj=Book.objects.filter(pk=1).first()
# book_obj.publish is the publishing object associated with the book object with the primary key of 1
print(book_obj.publish.city) 

Reverse query by book table

# Inquire the book name published by Mingjiao Publishing House
publish=Publish.objects.get(name="Mingjiao press")
#publish.book_set.all(): collection of all book objects associated with Mingjiao Publishing House
book_list=publish.book_set.all()    
for book_obj in book_list:
       print(book_obj.title)

2. One to one query (Author and AuthorDetail)

Forward query (by field: Au detail):

# Inquire Linghu Chong's phone number
ling=Author.objects.filter(name="linghu chong").first()
print(ling.au_detail.tel)

Reverse query (by table name: author):

# Find out the names of all the authors whose addresses are in heimuya
 
authorDetail_list=AuthorDetail.objects.filter(addr="Blackwood cliff")
for obj in authorDetail_list:
     print(obj.author.name)

3. Many to many query (Author and Book)

Forward query (by field: authors):

# All the authors' names and mobile phone numbers of Dugu Jiujian

book_obj = Book.objects.filter(title="Dugu nine Swords").first()
authors = book_obj.authors.all()
for author_obj in authors: 
    print(author_obj.name, author_obj.au_detail.tel)

Reverse query (by table name: Book Set):

# Query the names of all the books that Linghu rushed out

    author_obj=Author.objects.get(name="linghu chong")
    book_list=author_obj.book_set.all()        #All books related to the author of Ling Huchong
    for book_obj in book_list:
        print(book_obj.title)

Note:

You can override the name of foo'u set by setting the value of related'name in the definitions of ForeignKey() and ManyToManyField. For example, if you make the following changes in the Book model:

publish = models.ForeignKey("Publish", on_delete=models.CASCADE, related_name="book_list")

Then we will see as follows:

 # Query all books published by Mingjiao Publishing House

 publish=Publish.objects.get(name="Mingjiao press")
 book_list=publish.book_list.all()  # Collection of all book objects associated with Mingjiao Publishing House

5. Cross table query based on double underscores

Django also provides an intuitive and efficient way to express the association relationship in the query (lookups), which can automatically confirm the SQL JOIN connection. To do cross relationship queries, use two underscores to link the names of the associated fields between models until you finally link to the model you want.

'''
    Forward query by field, reverse query by table name lowercase to tell ORM engine which table to join
'''

1. One to many query

# Practice:  Check the names and prices of all books published by Mingjiao press(One to many)

    # Forward query by field:publish

    queryResult=Book.objects.filter(publish__name="Ming religionpress").values_list("title","price")

    # Reverse query by table name:book

    queryResult=Publish.objects.filter(name="Ming religionpress").values_list("book__title","book__price")

2. Many to many query

# Practice: Query the names of all the books that Linghu rushed out(Many to many)

    # Forward query by field:authors:
    queryResult=Book.objects.filter(authors__name="linghu chong").values_list("title")

    # Reverse query by table name:book
    queryResult=Author.objects.filter(name="linghu chong").values_list("book__title","book__price")

3. One to one query

# Query Ling Hu Chong's mobile number
    
    # Forward query
    ret=Author.objects.filter(name="linghu chong").values("au_detail__tel") 

　　# inverse query 
　　ret=AuthorDetail.objects.filter(author__name="linghu chong").values("tel")

4. Advanced practice (continuous span table)

# Practice: Check the names of all the books published by Mingjiao publishing house and the names of the authors


    # Forward query
    queryResult=Book.objects.filter(publish__name="Ming religionpress").values_list("title","authors__name")
    # inverse query
    queryResult=Publish.objects.filter(name="Ming religionpress").values_list("book__title","book__authors__age","book__authors__name")


# Practice: Names of all books published by authors with mobile numbers starting with 132 and the name of the publishing house


    # Mode 1:
    queryResult=Book.objects.filter(authors__au_detail__tel__startswith="132").values_list("title","publish__name") 

　　 # Mode 2:  
　　 ret=Author.objects.filter(au_detail__tel__startswith="132").values("book__title","book__publish__name")

6. Aggregate query and group query

1. aggregate query

Let's feel it through an example.

# Calculate the average price of all books
books = models.Book.objects.aggregate(Avg("price"))
books = models.Book.objects.aggregate(avg_price=Avg("price"))  # Specify the key of the dictionary as avg_

aggregate() is a termination clause of QuerySet, which means that it returns a dictionary containing some key value pairs. The name of the key is the identifier of the aggregate value, and the value is the calculated aggregate value. The name of the key is automatically generated by the name of the field and aggregate function. If you want to specify a name for the aggregate value, you can specify it to the aggregate clause (as in the example above).

If you want to generate more than one aggregate, you can add another parameter to the aggregate() clause. So, if you want to know the maximum and minimum prices of all books, you can query as follows:

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

# Calculate the average, most expensive, and least expensive prices of all books
books = models.Book.objects.aggregate(Avg("price"), Max("price"), Min("price"))

2. Group query annotate

Before we talk about it, let's recall how to query the SQL statements we learned before. Let's compare ORM code to deepen our understanding.

Let's practice the single watch first:

The table structure is:

class Emp(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    salary = models.DecimalField(max_digits=8, decimal_places=2)
    dep = models.CharField(max_length=32)
    province = models.CharField(max_length=32)

Prepare data:

INSERT INTO `bkm`.`app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('1', 'linghu chong', '24', '6000.00', 'Sales Department', 'Henan');
INSERT INTO `bkm`.`app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('2', 'Ren yingying', '18', '8000.00', 'Guangong Department', 'Guangdong');
INSERT INTO `bkm`.`app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('3', 'ren woxing', '56', '10000.00', 'Sales Department', 'Guangdong');
INSERT INTO `bkm`.`app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('4', 'Yue Lingshan', '19', '6000.00', 'Guangong Department', 'Henan');
INSERT INTO `bkm`.`app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('5', 'little dragon maiden', '20', '8000.00', 'Guangong Department', 'Hebei');

Query operation:

# Query the name of each department and the corresponding number of employees
SQL:
select dep, count(1) from emp group by dep;
ORM:
models.Emp.objects.values('dep').annotate(c=Count('id'))

# Query the name of each department and the average salary of the corresponding employee
SQL: 
select dep, avg(salary) from app01_emp GROUP BY dep;
ORM:
models.Emp.objects.values('dep').annotate(a=Avg('salary'))

OK, next. Let's play group query with multiple tables.

The table structure is:

class Emps(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    salary = models.DecimalField(max_digits=8, decimal_places=2)
    dep = models.ForeignKey("Dep", on_delete=models.CASCADE)
    province = models.CharField(max_length=32)


class Dep(models.Model):
    title = models.CharField(max_length=32)

Prepare data:

1. Dep table:

INSERT INTO `bkm`.`app01_dep` (`id`, `title`) VALUES ('1', 'Sales Department');
INSERT INTO `bkm`.`app01_dep` (`id`, `title`) VALUES ('2', 'Guangong Department');

2. Emps table:

INSERT INTO `bkm`.`app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('2', 'linghu chong', '24', '8000.00', 'Henan', '1');
INSERT INTO `bkm`.`app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('3', 'Ren yingying', '18', '9000.00', 'Guangdong', '2');
INSERT INTO `bkm`.`app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('4', 'ren woxing', '57', '10000.00', 'Guangdong', '1');
INSERT INTO `bkm`.`app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('5', 'Yue Lingshan', '19', '6000.00', 'Henan', '2');
INSERT INTO `bkm`.`app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('6', 'little dragon maiden', '20', '8000.00', 'Hebei', '2');

Query operation:

# Query the name of each department and the corresponding number of employees

SQL:
SELECT app01_dep.title,count(app01_emps.id) FROM app01_emps LEFT JOIN app01_dep ON app01_emps.dep_id = app01_dep.id GROUP BY app01_emps.dep_id;

ORM:
models.Emps.objects.values("dep__title").annotate(c=Count("id"))

# Query the name of each department and the average salary of the corresponding employee

SQL:
SELECT app01_dep.title,avg(app01_emps.salary) FROM app01_emps LEFT JOIN app01_dep ON app01_emps.dep_id = app01_dep.id GROUP BY app01_emps.dep_id;

ORM:
models.Emps.objects.values("dep__title").annotate(a=Avg("salary"))

Summary: the essence of cross table group query is to join related tables into a table, and then group query according to the idea of single table.

3. Inquiry exercise

1. Practice: count the price of the cheapest book of each publishing house

# Exercise: count the price of the cheapest book in each publishing house
    SQL:
    select name, min(app01_book.price)
    from app01_publish LEFT
    JOIN
    app01_book
    on
    app01_book.publish_id = app01_publish.id
    GROUP
    BY
    app01_publish.name;

    ORM:
models.Publish.objects.values("name").annotate(min_price=Min("book__price"))

2. Exercise: count the number of authors of each book

Exercise: count the number of authors of each book
SQL:
SELECT
    title,
    count(NAME)
FROM
    app01_book
LEFT JOIN app01_book_authors ON app01_book.id = app01_book_authors.book_id
LEFT JOIN app01_author ON app01_book_authors.author_id = app01_author.id
GROUP BY
    app01_book.id;

ORM:
models.Book.objects.annotate(num=Count('authors__name')).values("title", "num")

3. Exercise: count the number of authors of each book beginning with "Nine"

# Exercise: count the number of authors of each book beginning with "Nine"
SQL:
SELECT
    title,
    count(NAME)
FROM
    app01_book
LEFT JOIN app01_book_authors ON app01_book.id = app01_book_authors.book_id
LEFT JOIN app01_author ON app01_book_authors.author_id = app01_author.id
WHERE
    app01_book.title LIKE 'Nine%'
GROUP BY
    app01_book.id;

ORM:
models.Book.objects.filter(title__startswith="Nine").annotate(num=Count('authors__name')).values("title", "num")

4. Exercise: count the book names of more than one author

# Exercise: count books by more than one author

SQL:
SELECT
    title,
    count(NAME) AS num
FROM
    app01_book
LEFT JOIN app01_book_authors ON app01_book.id = app01_book_authors.book_id
LEFT JOIN app01_author ON app01_author.id = app01_book_authors.author_id
GROUP BY
    app01_book.id
HAVING
    num > 1;

ORM:
models.Book.objects.annotate(num_author=Count("authors__name")).filter(num_author__gt=1).values("title")

5. Exercise: sort the query set by the number of authors of a Book

# Exercise: pair query sets according to the number of authors in a Book QuerySet Sort

SQL:
SELECT
    title,
    count(author_id) AS num
FROM
    app01_book
LEFT JOIN app01_book_authors ON app01_book.id = app01_book_authors.book_id
LEFT JOIN app01_author ON app01_author.id = app01_book_authors.author_id
GROUP BY
    app01_book.id
ORDER BY
    num;

ORM:
models.Book.objects.annotate(num_author=Count("authors__name")).order_by("num_author").values("title", "num_author")

6. Exercise: query the total price of books published by each author

# Exercise: find out the total price of each author's book

SQL:
SELECT
    NAME,
    sum(price)
FROM
    app01_author
LEFT JOIN app01_book_authors ON app01_author.id = app01_book_authors.author_id
LEFT JOIN app01_book ON app01_book.id = app01_book_authors.book_id
GROUP BY
    app01_author.id;

ORM:
models.Author.objects.annotate(total=Sum("book__price")).values('name', 'total')

7. F query and Q query

1. F query

In all of the above examples, the filter we constructed simply compares a field value to a constant. If we want to compare the values of two fields, what should we do?

Django provides F() to make such a comparison. The instance of F() can reference fields in the query to compare the values of two different fields in the same model instance.

# Query the person whose salary is greater than age
from django.db.models import F, Q

models.Emp.objects.filter(salary__gt=F('age'))

Django supports addition, subtraction, multiplication, division and modulo operations between F() objects and between F() objects and constants.

# Query people whose salary is less than twice the age value

models.Emp.objects.filter(salary__lt=F("age") * 2)

F function can also be used for modification, such as increasing the price of each book by 100 yuan

models.Book.objects.update(price=F('price') + 100)

2. Q query

Keyword parameter queries in filter() AND other methods are "AND" together. If you need to perform more complex queries (such as OR statements), you can use the Q object.

from django.db.models import Q

Q(title__startswith="Nine")

Q objects can be combined with &, | and ￣ (and or not) operators. When an operator is used on two Q objects, it produces a new Q object.

# Search for books whose price is more than 300 or whose name begins with nine
    
models.Book.objects.filter(Q(price__gt=300)|Q(title__startswith="Nine"))

Equivalent to the following SQL where clause:

WHERE (`app01_book`.`price` > 300 OR `app01_book`.`title` LIKE BINARY 'Nine%') 

For comprehensive use, see the example:

# Search for books with a price greater than 300 or not march 2019

models.Book.objects.filter(Q(price__gt=300)|~Q(Q(pub_date__year=2019)&Q(pub_date__month=3)))