Django模型层之多表操作

作者模型：一个作者有姓名和年龄。

作者详细模型：把作者的详情放到详情表，包含生日，手机号，家庭住址等信息。作者详情模型和作者模型之间是一对一的关系（one-to-one）

出版商模型：出版商有名称，所在城市以及email。

书籍模型： 书籍有书名和出版日期，一本书可能会有多个作者，一个作者也可以写多本书，所以作者和书籍的关系就是多对多的关联关系(many-to-many);一本书只应该由一个出版商出版，所以出版商和书籍是一对多关联关系(one-to-many)。

具体的模型如下：(models.py)

  1: from django.db import models
  2: 
  3: # Create your models here.
  4: 
  5: 
  6: class Author(models.Model):
  7:     nid = models.AutoField(primary_key=True)
  8:     name = models.CharField(max_length=32)
  9:     age = models.IntegerField()
 10: 
 11:     # 与AuthorDetail建立一对一的关系
 12:     authorDetail = models.OneToOneField(to="AuthorDetail", on_delete=models.CASCADE)
 13: 
 14:     def __str__(self):
 15:         return self.name
 16: 
 17: 
 18: class AuthorDetail(models.Model):
 19: 
 20:     nid = models.AutoField(primary_key=True)
 21:     birthday = models.DateField()
 22:     telephone = models.BigIntegerField()
 23:     addr = models.CharField(max_length=64)
 24: 
 25: 
 26: class Publish(models.Model):
 27:     nid = models.AutoField(primary_key=True)
 28:     name = models.CharField(max_length=32)
 29:     city = models.CharField(max_length=32)
 30:     email = models.EmailField()
 31: 
 32: 
 33: class Book(models.Model):
 34: 
 35:     nid = models.AutoField(primary_key=True)
 36:     title = models.CharField(max_length=32)
 37:     publishDate = models.DateField()
 38:     price = models.DecimalField(max_digits=5, decimal_places=2)
 39: 
 40:     # 与Publish建立一对多的关系,外键字段建立在多的一方
 41:     publish = models.ForeignKey(to="Publish", to_field="nid", on_delete=models.CASCADE)
 42:     # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表
 43:     authors = models.ManyToManyField(to='Author',)
 44: 
 45:     def __str__(self):
 46:         return self.title

其他的准备工作见单表操作。自动生成的表如下：

+----------------------------+
| Tables_in_db4             |
+----------------------------+
| app01_author              |
| app01_authordetail     |
| app01_book                 |
| app01_book_authors   |
| app01_publish              |
| auth_group                   |
| auth_group_permissions|
| auth_permission            |
| auth_user                      |
| auth_user_groups         |
| auth_user_user_permissions |
| django_admin_log          |
| django_content_type     |
| django_migrations          |
| django_session               |
+----------------------------+

添加记录

一对多添加记录：

urls.py

  1: from django.contrib import admin
  2: from django.urls import path
  3: from app01 import views
  4: urlpatterns = [
  5:     path('admin/', admin.site.urls),
  6:     path('add/', views.add),
  7: ]

views.py

  1: from django.shortcuts import render, HttpResponse
  2: # Create your views here.
  3: from app01.models import *
  4: def add(request):
  5: 
  6:     pub = Publish.objects.create(name="人民出版社", email='123@qq.com', city='Beijing')
  7:     # 方式1
  8:     # 给book表绑定关系：publish
  9:     book_obj = Book.objects.create(title='红楼梦', price=100, publishDate='2012-12-12', publish_id=1)  # 注意这里是publish_id
 10:     print(book_obj.title)
 11: 
 12:     # 方式2
 13:     pub_obj = Publish.objects.filter(nid=1)[0]
 14:     book_obj = Book.objects.create(title='三国演义', price=100, publishDate='2012-12-12', publish=pub_obj)
 15:     print(book_obj.publish)  # 与这本书关联的出版社对象！这里是ORM的重点
 16:     print(book_obj.publish_id)
 17:     print(book_obj.publishDate)
 18:     return HttpResponse("OK!")

再添加一本三国演义和西游记：

  1: pub_obj = Publish.objects.filter(nid=1)[0]
  2: book_obj = Book.objects.create(title='三国演义', price=100, publishDate='2012-12-12', publish=pub_obj)
     book_obj_2 = Book.objects.create(title='西游记', price=100, publishDate='2012-12-12', publish=pub_obj)

查询西游记的出版社对应的邮箱

  1: book_obj = Book.objects.filter(title="西游记")[0]
  2: print(book_obj.publish.email)

多对多添加记录：

  1:     # 绑定多对多的关系
  2:     book_obj = Book.objects.create(title='金pin梅', price=100, publishDate='2012-12-12', publish_id=1)
  3:     alex = Author.objects.get(nid=1)
  4:     egon = Author.objects.get(name='egon')
  5:     # 绑定关系的API的几种写法
  6:     book_obj.authors.add(egon, alex)
  7:     book_obj.authors.add(1, 2)   # 作者1，作者2
  8:     book_obj.authors.add(*[1, 2])
  9:     # 解除多对多关系
 10:     book = Book.objects.filter(nid=4)[0]
 11:     book.author.remove(1, 2)  # 参数类似绑定方法
 12:     book.author.clear()  # 清空所有
 13:     # 查询与这本书关联的所有作者对象(Queryset)
 14:     print(book.authors.all())
 15:     ret = book.authors.all().values("name")
 16:     print(ret)

跨表查询

基于对象的跨表查询：

一对多

  1: def query(request):
  2: 
  3:     # 基于对象的跨表查询(子查询)
  4:     # 一对多的正向查询：查询金pin梅这本书的出版社的名字：
  5:     book_obj = Book.objects.filter(title='金pin梅')[0]
  6:     print(book_obj.publish.name)
  7:     # 一对多的反向查询：查询人民出版社出版过的书籍名称
  8:     publish_obj = Publish.objects.filter(name='人民出版社')[0]
  9:     print(publish_obj.book_set.all())
 10: 
 11:     return HttpResponse("OK!")

多对多

  1:     # 多对多的正向查询：查询金pin梅这本书的作者的名字
  2:     book_obj = Book.objects.filter(title='金pin梅')[0]
  3:     for author in book_obj.authors.all():
  4:         print(author.name)
  5:     # 多对多的反响查询：查询alex出版社的所有书籍
  6:     alex_obj = Author.objects.filter(name='alex')[0]
  7:     for book in alex_obj.book_set.all():
  8:         print(book.title)

一对一

  1:     # 一对一的正向查询：查询alex的手机号
  2:     alex_obj = Author.objects.filter(name='alex')[0]
  3:     print(alex_obj.authorDetail.telephone)
  4:     # 一对一的反向查询： 查询手机号为110的作者名字和年龄
  5:     telephone_obj = AuthorDetail.objects.filter(telephone='110')[0]
  6:     print('%s-%s' % (telephone_obj.author.name, telephone_obj.author.age))

总结：

# 一对多查询:

# A-B:关联属性在A表中

# 正向查询：A(Book)----->B(Publish) ->book_obj.publish

# 反向查询(表名小写_set.all())：B(Publish)----->A(Book) ->publish_obj.book_set.all()

# 多对多查询:

# 正向查询：A(Book:关联属性authors) ----> B(Author) -> book_obj.authors.all()

# 反向查询：B ------> A -> Author.book_set.all()

# 一对一查询：

# 正向查询：A(Author) ----> B(AuthorDetail) -> book_obj.authordetail

# 反向查询：B ------> A -> authordetail.author

基于双下划线跨表查询：

正向查询按字段(模型对象名称+__+字段名)。反向查询按表名小写，用来告诉ORM引擎join哪张表

一对多查询的正向查询：查询金pin梅这本书的出版社的名字

# 方式1：

  1: Book.objects.filter(title='金pin梅').values('publish__name')

# 方式2：

  1: Publish.objects.filter(book__title="金pin梅").values('name')

# 两个方法对应同样的sql语句（仅仅inner join左右表名次序不一样）

  1: select app01_publish.name from app01_book INNER JOIN app01_publish
  2: on app01_book.publish_id = app01_publish.nid
  3: where app01_book.title = "金pin梅";

多对多查询：查询金pin梅这本书所有作者的名字

# 方法1：

# 通过Book表join与其关联的Author表，属于正向查询，按字段authors通知ORM引擎join book_authors与author表

# PS:authors是models里面的对象

  1: Book.objects.filter(title="金pin梅").values('authors__name')

# 方法2：

# 通过Author表join与其关联的Book表，属于反向查询：按表名小写book通知ORM引擎join book_author与book表

  1: Author.objects.filter(book__title='金pin梅').values("name")

#sql语句（两种有细微的不同，大致上一样）

  1: select app01_author.name from app01_book INNER JOIN app01_book_authors
  2: on app01_book.nid = app01_book_authors.book_id
  3: INNER JOIN app01_author
  4: on app01_book_authors.author_id = app01_author.nid
  5: where app01_book.title = "金pin梅";

一对一查询：查询alex的手机号

# 方法1

  1: Author.objects.filter(name='alex').values('authorDetail__telephone')

# 方法2

  1: AuthorDetail.objects.filter(author__name='alex').values('telephone')

连续跨表：

手机号以110开头的作者出版过的所有书籍名称以及出版社名称

# 方法1：通过Book表join AuthorDetail表，Book与AuthorDetail无关联，所以必须连表

  1: Book.objects.filter(authors__authorDetail__telephone__startswith='110').values("title", 'publish__name')

# 方法2：通过Author表为基表

  1: Author.objects.filter(authorDetail__telephone__startswith='110').values('book__title', 'book__publish__name')

聚合和分组查询：

聚合 aggregate(*args, **kwargs)

分组查询 annotate,返回值依然是queryset

# 查询所有书籍的平均价格:返回值是一个字典,键为前面的参数名。

  1: from django.db.models import Avg, Max, Min, Count
  2: print(Book.objects.all().aggregate(avg_price=Avg('price'), max_price=Max('price')))

单表下的分组查询：

添加一张新表，模型文件如下：

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

再执行数据库迁移命令，在数据库生成表。

# 查询每一个部门的名称以及员工的平均薪水：

  1: from django.db.models import Avg, Max, Min, Count
  2: print(Emp.objects.values('dep').annotate(ava_salary=Avg('salary')))

# 查询每一个省份的名称以及员工数

  1: print(Emp.objects.values("province").annotate(count_id=Count('id')))

多表下的分组查询:

# 查询每一个出版社的名称以及出版社的书籍个数

  1: print(Publish.objects.values("name").annotate(number=Count("book__title")))
  2: print(Publish.objects.values("nid").annotate(number=Count("book__title")).values("name", "number"))

sql语句：

  1: select app01_publish.name as publish_name, count(a01ba.book_id) as book_number from app01_publish
  2: inner join app01_book a01b
  3: on app01_publish.nid = a01b.publish_id
  4: inner join app01_book_authors a01ba
  5: on a01b.nid = a01ba.book_id
  6: group by app01_publish.nid;

建议使用第二种方式查询

# 查询每一个作者的名字以及出版过的书籍的最高价格（pk=primary key）

  1: print(Author.objects.values("pk").annotate(max_price=Max("book__price")).values("name", "max_price"))

sql语句：

  1: select app01_author.name as author, max(app01_book.price)from app01_author inner join app01_book_authors a01ba
  2: on app01_author.nid = a01ba.author_id
  3: inner join app01_book
  4: on a01ba.book_id = app01_book.nid
  5: group by app01_author.nid;

# 查询每一个书籍的名称以及对应的作者个数

  1: print(Book.objects.values("pk").annotate(writer_number=Count("authors__name")).values("title", "writer_number"))

sql:

  1: select app01_book.title, count(a01ba.author_id) from app01_book inner join app01_book_authors a01ba on app01_book.nid = a01ba.book_id
  2: inner join app01_author a01a on a01ba.author_id = a01a.nid
  3: group by app01_book.nid;

跨表的分组查询的模型总结：

1.每一个后的表模型.objects.values("pk").annotate(查询项目=聚合函数("关联表__统计字段")).values("表模型的所有字段以及统计字段")
2.每一个后的表模型.objects.all().annotate(查询项目=聚合函数("关联表__统计字段")).values("表模型的所有字段以及统计字段")
Ps:all()可以不加

几个小练习：

  1: # 查询每一本以py开头的书籍的作者个数
  2: print(Book.objects.filter(title__startswith="py").values("pk").annotate(c=Count("authors__name")).values("title", "c"))
  3: # 统计不止一个作者的书籍
  4: print(Book.objects.values("pk").annotate(c=Count("authors__name")).filter(c__gt=1).values("title"))
  5: # 根据一本图书作者数量的多少对查询集 QuerySet进行排序:
  6: print(Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors'))
  7: # 查询各个作者出的书的总价格:
  8: print(Author.objects.annotate(SumPrice=Sum("book__price")).values_list("name", "SumPrice"))

F查询与Q查询：

准备工作：在模型文件Book类中添加

  1: read_num = models.IntegerField(default=0)
  2: comment_num = models.IntegerField(default=0)

执行数据库迁移的两道命令后刷新，app01_book表增加了两个字段。再随机填一些数字上去。

F查询：

1.F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。

2.Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。

3.修改操作也可以使用F函数,比如将每一本书的价格提高30元。

  1: from django.db.models import F, Q
  2: # 查询评论数大于阅读数的书籍
  3: print(Book.objects.filter(comment_num__gt=F("read_num")))
  4: # 查询评论数大于收藏数2倍的书籍
  5: print(Book.objects.filter(comment_num__lt=F('read_num') * 2))
  6: # 所有书的价格加一
  7: Book.objects.all().update(price=F("price")+1)
  8: # 查询title=“红楼梦或者price=101的书籍
  9: print(Book.objects.filter(Q(title="红楼梦") | Q(price=101)))

Q查询：

1.filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如OR 语句），你可以使用Q 对象。

2.Q 对象可以使用& 和| 操作符组合起来。当一个操作符在两个Q 对象上使用时，它产生一个新的Q 对象。

  1: bookList=Book.objects.filter(Q(authors__name="yuan")|Q(authors__name="egon"))

3.你可以组合& 和| 操作符以及使用括号进行分组来编写任意复杂的Q 对象。同时，Q 对象可以使用~ 操作符取反，这允许组合正常的查询和取反(NOT) 查询：

  1: bookList=Book.objects.filter(Q(authors__name="yuan") & ~Q(publishDate__year=2017)).values_list("title")

4.查询函数可以混合使用Q 对象和关键字参数。所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。但是，如果出现Q 对象，它必须位于所有关键字参数的前面。例如：

  1: bookList=Book.objects.filter(Q(publishDate__year=2016) | Q(publishDate__year=2017),
  2:                               title__icontains="python"
  3:                              )