Django 多表操作

Django 多表操作

一表关系回顾

在讲解MySQL时，我们提到，把应用程序的所有数据都放在一张表里是极不合理的。

比如我们开发一个员工管理系统，在数据库里只创建一张员工信息表，该表有四个字段：工号、姓名、部门名、部门职能描述，此时若公司有1万名员工，但只有3个部门，因为每一名员工后都需要跟着部门信息（部门名、部门职能），所以将会导致部门信息出现大量重复、浪费空间。

解决方法就是将数据存放于不同的表中，然后基于foreign key建立表之间的关联关系。

细说的话，表之间存在三种关系：多对一、一对一、多对多，那如何确定两张表之间的关系呢？按照下述步骤操作即可

左表<------------------------------->右表

# 步骤一：先分析
#分析1、先站在左表的角度
是否左表的多条记录可以对应右表的一条记录

#分析2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录

# 步骤二：后确定关系
# 多对一
如果只有"分析1"成立，那么可以确定两张表的关系是：左表多对一右表，关联字段应该创建在左表中，然后foreign key 右表一个字段（通常是id）
如果只有"分析2"成立，那么可以确定两张表的关系是：右表多对一左表，关联字段应该创建在右表中，然后foreign key 左表一个字段（通常是id）

# 一对一
如果"分析1"和"分析2"都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的关联字段设置成unique即可

# 多对多
如果"分析1"和"分析2"同时成立，则证明这两张表是一个双向的多对一，即多对多,需要创建一张单独的新表来专门存放二者的关系，关联字段应该创建在新表中，然后在新表中分别foreign key两张表的id字段

我们以一个图书管理系统为背景，设计了下述四张表，让我们来找一找它们之间的关系

书籍表：app01_book

出版社表：app01_publish

作者表：app01_author

作者详细信息表：app01_authordetail

app01_book与app01_publish

找关系

左表(app01_book)<------------------------------->右表(app01_publish)

# 步骤一：
#分析1、先站在左表的角度
左表的多条记录代表多版本书籍，右表的一条记录代表一个出版社，多本书籍对应同一个出版社 ✔️

#分析2、再站在右表的角度去找
右表的多条记录代表多个出版社，左表的一条记录代表一本书，多个出版社不能出版同一本书 ✘

# 步骤二：后确定关系
# 多对一
只有"分析1"成立，那么可以确定两张表的关系是：左表(app01_book)多对一右表(app01_publish)，关联字段应该创建在左表(app01_book)中，然后foreign key 右表(app01_publish)的id字段

sql语句

# 1、由于foreign key的影响,必须先创建被关联表
CREATE TABLE app01_publish (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR (20)
);

# 2、才能创建出关联表
CREATE TABLE app01_book (
    id INT PRIMARY KEY auto_increment,
    title VARCHAR (20),
    price DECIMAL (8, 2),
    pub_date DATE,
    publish_id INT, # 新增关联字段
    FOREIGN KEY (publish_id) REFERENCES app01_publish (id) 
    ON UPDATE CASCADE ON DELETE CASCADE
);

app01_author与app01_authordetail

找关系

左表(app01_author)<------------------------------->右表(app01_authordetail)

一个作者唯一对应一条自己的详情信息，反之亦然，所以两张表是一对一的关系。在左表中新增关联字段并添加unique约束，然后foreign key右表

sql语句

# 1、由于foreign key的影响,必须先创建被关联表
CREATE TABLE app01_authordetail (
    id INT PRIMARY KEY auto_increment,
    tel VARCHAR (20)
);

# 2、才能创建出关联表
CREATE TABLE app01_author (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR (20),
    age INT,
    authordetail_id INT UNIQUE, # 新增关联字段,并添加唯一性约束unique
    FOREIGN KEY (authordetail_id) REFERENCES app01_authordetail (id) 
    ON UPDATE CASCADE ON DELETE CASCADE
);

app01_book与app01_author

找关系

左表(app01_book)<------------------------------->右表(app01_author)

# 步骤一：
#分析1、先站在左表的角度
左表的多条记录代表多版本书籍，右表的一条记录代表一个作者，多本书籍可以由同一个作者编写 ✔️

#分析2、再站在右表的角度去找
右表的多条记录代表多个作者，左表的一条记录代表一本书，多个作者可以合作编写同一本书 ✔️

# 步骤二：后确定关系
# 多对多
"分析1"和"分析2"同时成立，证明这两张表是多对多的关系,需要创建一张单独的新表来专门存放二者的关系，关联字段应该创建在新表中，然后在新表中分别foreign key两张表的id字段

sql语句

# 1、创建被关联表一：app01_book，例1中已创建
# 2、创建被关联表二：app01_author，例2中已创建

# 3、创建新表，存放app01_book于app01_author的关联关系
CREATE TABLE app01_book_authors (
    id INT PRIMARY KEY auto_increment,
    book_id INT, # 新增关联字段，用来关联表app01_book
    author_id INT, # 新增关联字段，用来关联表app01_author
    FOREIGN KEY (book_id) REFERENCES app01_book (id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (author_id) REFERENCES app01_author (id) ON UPDATE CASCADE ON DELETE CASCADE
);

上述三个例子中生成的表如下

插图5

二创建模型

模型类如下

from django.db import models

# 表app01_publish
class Publish(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=20)


# 表app01_book
class Book(models.Model):
    nid = models.AutoField(primary_key=True)
    title = models.CharField(max_length=20)
    price = models.DecimalField(max_digits=8, decimal_places=2)
    pub_date = models.DateField()

    # 表app01_book多对一表app01_publish，参数to指定模型名，参数to_field指定要关联的那个字段
    publish = models.ForeignKey(to='Publish',to_field='nid',on_delete=models.CASCADE)

    # 我们自己写sql时，针对书籍表与作者表的多对关系，需要自己创建新表，而基于django的orm，下面这一行代码可以帮我们自动创建那张关系表
    authors=models.ManyToManyField(to='Author') 
    # 变量名为authors，则新表名为app01_book_authors，若变量名为xxx，则新表名为app01_book_xxx


# 表app01_author
class Author(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=20)
    age = models.IntegerField()

    # 表app01_author一对一表app01_authordetail
    author_detail = models.OneToOneField(to='AuthorDetail',to_field='nid',unique=True,on_delete=models.CASCADE)


# 表app01_authordetail
class AuthorDetail(models.Model):
    nid = models.AutoField(primary_key=True)
    tel = models.CharField(max_length=20)

强调：

在创建关联时，针对参数to，如果传入的是字符串（to="模型名"），则模型类的定义不区分先后顺序，如果传入的是模型名（to=Author），则Author类必须事先定义

注意事项：

表的名称myapp_modelName，是根据模型中的元数据自动生成的，也可以覆写为别的名称　　
id 字段是自动添加的
对于外键字段，Django 会在字段名上添加"_id" 来创建数据库中的列名
这个例子中的CREATE TABLE SQL 语句使用PostgreSQL 语法格式，要注意的是Django 会根据settings 中指定的数据库类型来使用相应的SQL 语句。
定义好模型之后，你需要告诉Django _使用_这些模型。你要做的就是修改配置文件中的INSTALL_APPSZ中设置，在其中添加models.py所在应用的名称。
外键字段 ForeignKey 有一个 null=True 的设置(它允许外键接受空值 NULL)，你可以赋给它空值 None 。
关联字段与外键约束没有必然的联系（建管理字段是为了进行查询，建约束是为了不出现脏数据）

三添加、删除、修改记录

3.1 添加记录

插图5

！！强调！！：上图所示的表名、字段名都是mysql中的真实表/物理表，而我们下述所示所有操作，都是通过模型类来操作物理表，例如无论增删改查，所使用的字段名都模型类中的字段

按照上图所示，由于foreign key的关系，我们需要事先往app01_publish与app01_authordetail里插入记录

# 1、需求：通过模型Publish往表app01_publish里插入三家出版社
Publish.objects.create(name='北京出版社')
Publish.objects.create(name='长春出版社')
Publish.objects.create(name='大连出版社')

# 2、需求：通过模型AuthorDetail往表app01_authordetail里插入三条作者详情
AuthorDetail.objects.create(tel='18611312331')
AuthorDetail.objects.create(tel='15033413881')
AuthorDetail.objects.create(tel='13011453220')

按照上图所示，插入时会涉及到多张表，我们同样分三种情况来介绍

1、多对一：app01_book与app01_publish

# 需求：书籍(葵花宝典、菊花宝典、桃花宝典)都是在北京出版社出版的
# 1、先通过模型Publish从出版社表app01_publish查出北京出版社
publish_obj=Publish.objects.filter(name='北京出版社').first()
# 上述代码也可以简写为：publish_obj=Publish.objects.get(name='北京出版社')

# 2、再通过模型Book往书籍表app01_book里插入三本书籍与出版社的对应关系
# 方式一：使用publish参数指定关联
book_obj1=Book.objects.create(title='葵花宝典',price=2000,pub_date='1985-3-11',publish=publish_obj)

book_obj2=Book.objects.create(title='菊花宝典',price=3000,pub_date='1990-1-21',publish=publish_obj)

book_obj3=Book.objects.create(title='桃花宝典',price=4000,pub_date='1991-1-23',publish=publish_obj)

# 方式二：使用publish_id参数指定关联
book_obj1=Book.objects.create(title='葵花宝典',price=2000,pub_date='1985-3-11',publish_id=publish_obj.nid)

book_obj2=Book.objects.create(title='菊花宝典',price=3000,pub_date='1990-1-21',publish_id=1) # 在已经出版社id的情况下，可以直接指定

book_obj3=Book.objects.create(title='桃花宝典',price=4000,pub_date='1991-1-23',publish_id=1)

# 注意：无论方式一还是方式二得到的书籍对象book_obj1、book_obj2、book_obj3
#      都可以调用publish字段来访问关联的那一个出版社对象
#      都可以调用publish_id来访问关联的那一个出版社对象的nid
print(book_obj1.publish,book_obj1.publish_id) 
print(book_obj2.publish,book_obj2.publish_id) 
print(book_obj3.publish,book_obj3.publish_id)
# 三本书关联的是同一个出版社，所以输出结果均相同

参照上述步骤，把剩余三本书与出版社的对应关系也插入

book_obj1 = Book.objects.create(title='玉女心经', price=5000, pub_date='1988-3-24', publish_id=2)

book_obj2 = Book.objects.create(title='玉男心经', price=3000, pub_date='1985-6-17', publish_id=2)

book_obj3 = Book.objects.create(title='九阴真经', price=6000, pub_date='1983-8-17', publish_id=3)

2、一对一：app01_author与app01_authordetail

# 需求：插入三个作者，并与作者详情表一一对应
# 由于作者详情表我们已经事先创建好记录，所以只需要往作者表插入记录即可
# 方式一：需要事先过滤出作者详情的对象，然后通过模型Author的字段author来指定要关联的作者详情对象(略)

# 方式二：确定作者详情对象的id，然后通过模型Author通过字段author_id来指定关联关系，
Author.objects.create(name='egon',age=18,author_detail_id=1)
Author.objects.create(name='kevin',age=38,author_detail_id=2)
Author.objects.create(name='rose',age=28,author_detail_id=3)

3、多对多：app01_book与app01_author

# 我们参照物理表app01_book_authors制定需求，需要创建如下关系
# 1、葵花宝典的作者为：egon、kevin
# 2、菊花宝典的作者为：egon、kevin、rose
# 3、桃花宝典的作者为：egon、kevin
# 4、玉女心经的作者为：kevin、rose
# 5、玉男心经的作者为：kevin
# 6、九阴真经的作者为：egon、rose

# 需要创建出上述关系，具体做法如下
# 1、先获取书籍对象
book_obj1=Book.objects.get(title='葵花宝典')
book_obj2=Book.objects.get(title='菊花宝典')
book_obj3=Book.objects.get(title='桃花宝典')
book_obj4=Book.objects.get(title='玉女心经')
book_obj5=Book.objects.get(title='玉男心经')
book_obj6=Book.objects.get(title='九阴真经')

# 2、然后获取作者对象
egon=Author.objects.get(name='egon')
kevin=Author.objects.get(name='kevin')
rose=Author.objects.get(name='rose')

# 3、最后依次创建上述关系：在原生SQL中多对多关系涉及到操作第三张关系表，但是在ORM中我们只需要操作模型类Book下的字段author即可
book_obj1.authors.add(egon,kevin)
book_obj2.authors.add(egon,kevin,rose)
book_obj3.authors.add(egon,kevin)
book_obj4.authors.add(kevin,rose)
book_obj5.authors.add(kevin)
book_obj6.authors.add(egon,rose)

可以通过书籍对象下的authors字段获取其所关联的所有作者对象

book_obj1.authors.all() # 返回一个存有多个作者的queryset

多对多关系其它常用API：

book_obj.authors.remove()      # 将某个特定的对象从被关联对象集合中去除。    ======   book_obj.authors.remove(*[])
book_obj.authors.clear()       #清空被关联对象集合
book_obj.authors.set()         #先清空再设置

3.2 删除、修改记录

# 1、book_obj.authors.remove() ：将某个特定的对象从被关联对象集合中去除
# 从菊花宝典的作者集合中去掉作者rose
rose = Author.objects.get(name='rose')
book_obj2 = Book.objects.get(title='菊花宝典')
book_obj2.authors.remove(rose)

# 2、book_obj.authors.clear()：清空被关联对象集合
# 清空菊花宝典所关联的所有作者
book_obj2 = Book.objects.get(title='菊花宝典')
book_obj2.authors.clear()

# 3、book_obj.authors.set()：先清空再重新设置　
# 玉男心经的作者原来为kevin，要求设置为egon、rose
egon=Author.objects.get(name='egon')
rose=Author.objects.get(name='rose')

book_obj5 = Book.objects.get(title='玉男心经')

book_obj5.authors.set([egon,rose]) # 多个作者对象放到列表里

四查询记录

数据库操作最常用的还是查询操作，在介绍ORM下多表关联查询时，需要事先记住关于ORM模型的一个非常重要的概念：在使用模型类进行多表关联查询时，如果确定两张表存在关联关系，那么在选取一个表作为起始（为了后续描述方便，我们将其简称为"基表"）后，可以跨表引用来自另外一张中的字段值，这存在正向与反向之分

如果关联字段存在于基表中，称之为正向查询，否则，称之为反向查询

例如表模Book与Publish，关联字段存在于Book中

# 当以Book为基表时，称之为正向查询
Book(基表)-------正向---------->Publish

# 当以Publish为基表时，称之为反向查询
Book<-------反向----------Publish(基表)

使用原生sql进行多表关联查询时无非两种方式：子查询、join连表查询，ORM里同样有两种查询方式（严格依赖正向、反向的概念）

4.1 基于对象的跨表查询

1、跨两张表查询

1.1、一对一查询（模型类Author与AuthorDetail）

正向查询，按关联字段：author_detail

# 需求：查询作者egon的手机号
# 1、先取出作者对象
egon=Author.objects.filter(name='egon').first() 
# 2、正向查询：根据作者对象下的关联字段author_detail取到作者详情
print(egon.author_detail.tel) # 输出：18611312331

反向查询，按模型名(小写)：author

# 需求：查询手机号为'18611312331'的作者名
# 1、先取出作者的详情对象
tel=AuthorDetail.objects.filter(tel='18611312331').first()
# 2、反向查询：根据小写的模型名author取到作者对象
print(tel.author.name) # 输出：egon

1.2、一对多查询（模型类Book与Publish）

正向查询，按关联字段：publish

# 需求：查询葵花宝典的出版社名字
# 1、先取书籍对象
book_obj=Book.objects.filter(title='葵花宝典').first()
# 2、正向查询：根据书籍对象下的关联字段publish取到出版社
print(book_obj.publish.name) # 输出：北京出版社

反向查询，按模型名(小写)_set：book_set

# 需求：查询北京出版社都出版的所有书籍名字
# 1、先取出出版社对象
publish_obj=Publish.objects.filter(name='北京出版社').first()
# 2、反向查询：根据book_set取到所有的书籍
book_objs=publish_obj.book_set.all()
print([book_obj.title for book_obj in book_objs]) # 输出：['葵花宝典', '菊花宝典', '桃花宝典']

1.3、多对多查询（模型类Book与Author）

正向查询，按关联字段，如authors

# 需求：查询葵花宝典的所有作者
# 1、先取出书籍对象
book_obj=Book.objects.filter(title='葵花宝典').first()
# 2、正向查询：根据书籍对象下的关联字段authors取到所有作者
author_objs=book_obj.authors.all()
print([obj.name for obj in author_objs]) # 输出：['egon', 'kevin']

反向查询，按模型名(小写)_set：如author_set

# 需求：查询作者rose出版的所有书籍
# 1、先取出作者对象
egon=Author.objects.filter(name='rose').first() 
# 2、反向查询：根据book_set取到作者对象
book_objs=egon.book_set.all()
print([book_obj.title for book_obj in book_objs]) # 输出：['玉女心经', '九阴真经', '玉男心经']

注意：

你可以通过在 ForeignKey() 和ManyToManyField的定义中设置 related_name 的值来覆写 FOO_set 的名称。例如，如果 Article model 中做一下更改：

publish = ForeignKey(Book, related_name='bookList')

那么接下来就会如我们看到这般:

# 查询 人民出版社出版过的所有书籍
 
publish=Publish.objects.get(name="人民出版社")
book_list=publish.bookList.all()  # 与人民出版社关联的所有书籍对象集合

2、连续跨>2张表查询

连续跨>2张表的操作的套路与上面的案例都是一样的

# 需求：查询葵花宝典的作者们的手机号

book_obj=Book.objects.filter(title='葵花宝典').first()
author_objs=book_obj.authors.all()
print([author_obj.author_detail.tel for author_obj in author_objs]) 
# 输出：['18611312331', '15033413881']

4.2 基于双下划线的跨表查询

Django 还提供了一种直观而高效的方式在查询(lookups)中表示关联关系，它能自动确认 SQL JOIN 联系。要做跨关系查询，就使用两个下划线来链接模型(model)间关联字段的名称，直到最终链接到你想要的model 为止。

'''
    正向查询按字段,反向查询按表名小写用来告诉ORM引擎join哪张表
'''

1、跨两张表查询

1.1、一对一查询（模型类Author与AuthorDetail）

正向查询，按关联字段+双下划线：author_detail__

# 需求：查询作者egon的手机号
# 注意values()中的参数是：关联字段名__要取的那张被关联表中的字段
res = Author.objects.filter(name='egon').values('author_detail__tel').first()
print(res['author_detail__tel']) # {'author_detail__tel': '18611312331'}

# 注意：基于双下划线的跨表查询会被django的orm识别为join操作，所以上述代码相当于如下sql，后续案例均是相同原理，我们不再累述
select app01_authordetail.tel from app01_author inner join app01_authordetail on app01_author.author_detail_id = app01_authordetail.nid where app01_author.name = 'egon';

反向查询，按模型名(小写)+双下划线：author__

# 需求：查询手机号为'18611312331'的作者名
# 注意values()中的参数是：小写的模型名__要取的那张被关联表中的字段
res=AuthorDetail.objects.filter(tel='18611312331').values('author__name').first()
print(res) # {'author__name': 'egon'}

补充：基表决定了正向还是反向

# 1、针对上例中正向查询的需求：查询作者egon的手机号，如果我们选取的基表是AuthorDetail，那么就成了反向查询，应该用反向查询的语法
res = AuthorDetail.objects.filter(author__name='egon').values('tel').first()
print(res)  # {'tel': '18611312331'}

# 2、针对上例中反向查询的需求：查询手机号为'18611312331'的作者名，如果我们选取的基表是Author，那么就成了正向查询，应该用正向查询的语法
res=Author.objects.filter(author_detail__tel='18611312331').values('name').first()
print(res) # {'name': 'egon'}

1.2、多对一查询（模型类Book与Publish）

正向查询，按关联字段+双下划线：publish__

# 需求：查询葵花宝典的出版社名字
# 注意values()中的参数是：关联字段名__要取的那张被关联表中的字段
res=Book.objects.filter(title='葵花宝典').values('publish__name').first()
print(res['publish__name']) # {'publish__name': '北京出版社'}

反向查询，按模型名(小写)+双下划线：book__

# 需求：查询北京出版社都出版的所有书籍名字
# 注意values()中的参数是：小写的模型名__要取的那张被关联表中的字段
res = Publish.objects.filter(name='北京出版社').values('book__title')
print(res)  # <QuerySet [{'book__title': '葵花宝典'}, {'book__title': '菊花宝典'}, {'book__title': '桃花宝典'}]>

补充：基表决定了正向还是反向

# 1、针对上例中正向查询的需求：查询葵花宝典的出版社名字，如果我们选取的基表是Publish，那么就成了反向查询，应该用反向查询的语法
res = Publish.objects.filter(book__title='葵花宝典').values('name').first()
print(res)  # {'name': '北京出版社'}

# 2、针对上例中反向查询的需求：查询北京出版社都出版的所有书籍名字，如果我们选取的基表是Book，那么就成了正向查询，应该用正向查询的语法
res=Book.objects.filter(publish__name='北京出版社').values('title')
print(res) # <QuerySet [{'title': '葵花宝典'}, {'title': '菊花宝典'}, {'title': '桃花宝典'}]>

1.3、多对多查询（模型类Book与Author）

正向查询，按关联字段+双下划线：authors__

# 需求：查询葵花宝典的所有作者
# 注意values()中的参数是：关联字段名__要取的那张被关联表中的字段
res=Book.objects.filter(title='葵花宝典').values('authors__name')
print(res) # <QuerySet [{'authors__name': 'egon'}, {'authors__name': 'kevin'}]>

反向查询，按模型名(小写)+双下划线：如book__

# 需求：查询作者rose出版的所有书籍
# 注意values()中的参数是：小写的模型名__要取的那张被关联表中的字段
res = Author.objects.filter(name='rose').values('book__title')
print(res) # <QuerySet [{'book__title': '玉女心经'}, {'book__title': '九阴真经'}, {'book__title': '玉男心经'}]>

补充：基表决定了正向还是反向

# 1、针对上例中正向查询的需求：查询葵花宝典的所有作者，如果我们选取的基表是authors，那么就成了反向查询，应该用反向查询的语法
res=Author.objects.filter(book__title='葵花宝典').values('name')
print(res) # <QuerySet [{'name': 'egon'}, {'name': 'kevin'}]>

# 2、针对上例中反向查询的需求：查询作者rose出版的所有书籍，如果我们选取的基表是Book，那么就成了正向查询，应该用正向查询的语法
res=Book.objects.filter(authors__name='rose').values('title')
print(res) # <QuerySet [{'title': '玉女心经'}, {'title': '九阴真经'}, {'title': '玉男心经'}]>

2、连续跨>2张表查询

连续跨>2张表的操作的套路与上面的案例都是一样的,可以连续接n个双下划线，只需要在每次连双下划线时，确定是正向还是反向即可

# 需求1：查询北京出版社出版过的所有书籍的名字以及作者的姓名、手机号
# 方式一：基表为Publish
res=Publish.objects.filter(name='北京出版社').values_list('book__title','book__authors__name','book__authors__author_detail__tel')

# 方式二：基表为Book
res=Book.objects.filter(publish__name='北京出版社').values_list('title','authors__name','authors__author_detail__tel')

# 循环打印结果均为
for obj in res:
    print(obj)
'''
输出：
('葵花宝典', 'egon', '18611312331')
('菊花宝典', 'egon', '18611312331')
('桃花宝典', 'egon', '18611312331')
('葵花宝典', 'kevin', '15033413881')
('菊花宝典', 'kevin', '15033413881')
('桃花宝典', 'kevin', '15033413881')
('菊花宝典', 'rose', '13011453220')
'''


# 需求2：查询手机号以186开头的作者出版过的所有书籍名称以及出版社名称
# 方式一：基表为AuthorDetail
res=AuthorDetail.objects.filter(tel__startswith='186').values_list('author__book__title','author__book__publish__name')

# 方式二：基表为Book
res=Book.objects.filter(authors__author_detail__tel__startswith='186').values_list('title','publish__name')

# 方式三：基表为Publish
res=Publish.objects.filter(book__authors__author_detail__tel__startswith='186').values_list('book__title','name')

# 循环打印结果均为
for obj in res:
    print(obj)
'''
输出：
('葵花宝典', '北京出版社')
('菊花宝典', '北京出版社')
('桃花宝典', '北京出版社')
('九阴真经', '大连出版社')
'''

4.3 related_name

publish = ForeignKey(Blog, related_name='bookList')

# 练习: 查询人民出版社出版过的所有书籍的名字与价格(一对多)

# 反向查询 不再按表名:book,而是related_name:bookList


    queryResult=Publish.objects
　　　　　　　　　　　　　　.filter(name="人民出版社")
　　　　　　　　　　　　　　.values_list("bookList__title","bookList__price")

反向查询时，如果定义了related_name ，则用related_name替换表名，例如：

五、 F与Q查询

F查询

在上面所有的例子中，我们在进行条件过滤时，都只是用某个字段与某个具体的值做比较。如果我们要对两个字段的值做比较，那该怎么做呢？

Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较两个不同字段的值，如下

# 一张书籍表中包含字段：评论数commentNum、收藏数keepNum，要求查询：评论数大于收藏数的书籍
from django.db.models import F
Book.objects.filter(commnetNum__lt=F('keepNum'))

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

# 查询评论数大于收藏数2倍的书籍
from django.db.models import F
Book.objects.filter(commnetNum__lt=F('keepNum')*2)

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

Book.objects.all().update(price=F("price")+30)

Q查询

filter() 等方法中逗号分隔开的多个关键字参数都是逻辑与(AND) 的关系。如果我们需要使用逻辑或(OR)来连接多个条件，就用到了Django的Q对象

可以将条件传给类Q来实例化出一个对象，Q的对象可以使用& 和| 操作符组合起来，&等同于and，|等同于or

from django.db.models import Q
Employee.objects.filter(Q(id__gt=5) | Q(name="Egon"))

# 等同于sql：select * from app01_employee where id < 5 or name = 'Egon';

Q 对象可以使用~ 操作符取反，相当于NOT

from django.db.models import Q
Employee.objects.filter(~Q(id__gt=5) | Q(name="Egon"))

# 等同于sql：select * from app01_employee where not (id < 5) or name = 'Egon';

当我们的过滤条件中既有or又有and，则需要混用Q对象与关键字参数，但Q 对象必须位于所有关键字参数的前面

from django.db.models import Q
Employee.objects.filter(Q(id__gt=5) | Q(name="Egon"),salary__lt=100)

# 等同于sql：select * from app01_employee where (id < 5 or name = 'Egon') and salary < 100;

六、聚合查询与分组查询

6.1 聚合查询

聚合查询aggregate()是把所有查询出的记录对象整体当做一个组，我们可以搭配聚合函数来对整体进行一个聚合操作

from django.db.models import Avg, Max, Sum, Min, Max, Count # 导入聚合函数

# 1. 调用objects下的aggregate()方法，会把表中所有记录对象整体当做一组进行聚合
res1=Employee.objects.aggregate(Avg("salary")) # select avg(salary) as salary__avg from app01_employee;
print(res1) # 输出：{'salary__avg': 70.73}

# 2、aggregate()会把QuerySet对象中包含的所有记录对象当成一组进行聚合
res2=Employee.objects.all().aggregate(Avg("salary")) # select avg(salary) as salary__avg from app01_employee;
print(res2) # 输出：{'salary__avg': 70.73}

res3=Employee.objects.filter(id__gt=3).aggregate(Avg("salary")) # select avg(salary) as salary__avg from app01_employee where id > 3;
print(res3) # 输出：{'salary__avg': 71.0}

aggregate()的返回值为字典类型，字典的key是由”聚合字段的名称___聚合函数的名称”合成的,例如

Avg("salary") 合成的名字为 'salary__avg'

若我们想定制字典的key名，我们可以指定关键参数，如下

res1=Employee.objects.all().aggregate(avg_sal=Avg('salary')) # select avg(salary) as avg_sal from app01_employee;

print(res1) # 输出：{'avg_sal': 70.73} # 关键字参数名就会被当做字典的key

如果我们想得到多个聚合结果，那就需要为aggregate传入多个参数

res1=Employee.objects.all().aggregate(nums=Count('id'),avg_sal=Avg('salary'),max_sal=Max('salary')) 
# 相当于SQL：select count(id) as nums,avg(salary) as avg_sal,max(salary) as max_sal from app01_employee;

print(res1) # 输出：{'nums': 10, 'avg_sal': 70.73, 'max_sal': Decimal('200.3')}

6.2 分组查询

分组查询annotate()相当于sql语句中的group by，是在分组后，对每个组进行单独的聚合，需要强调的是，在进行单表查询时，annotate()必须搭配values()使用：values("分组字段").annotate(聚合函数)，如下

# 表中记录
mysql> select * from app01_employee;
+----+-------+--------+------------+------------+--------+
| id | name  | gender | birth      | department | salary |
+----+-------+--------+------------+------------+--------+
|  1 | Egon  |      0 | 1997-01-27 | 财务部     |  100.1 |
|  2 | Kevin |      1 | 1998-02-27 | 技术部     |   10.1 |
|  3 | Lili  |      0 | 1990-02-27 | 运营部     |   20.1 |
|  4 | Tom   |      1 | 1991-02-27 | 运营部     |   30.1 |
|  5 | Jack  |      1 | 1992-02-27 | 技术部     |   11.2 |
|  6 | Robin |      1 | 1988-02-27 | 技术部     |  200.3 |
|  7 | Rose  |      0 | 1989-02-27 | 财务部     |   35.1 |
+----+-------+--------+------------+------------+--------+

# 查询每个部门下的员工数
res=Employee.objects.values('department').annotate(num=Count('id')) 
# 相当于sql：
# select department,count(id) as num from app01_employee group by department;

print(res) 
# 输出：<QuerySet [{'department': '财务部', 'num': 2}, {'department': '技术部', 'num': 3}, {'department': '运营部', 'num': 2}]>

跟在annotate前的values方法，是用来指定分组字段，即group by后的字段，而跟在annotate后的values方法，则是用来指定分组后要查询的字段，即select 后跟的字段

res=Employee.objects.values('department').annotate(num=Count('id')).values('num')
# 相当于sql：
# select count(id) as num from app01_employee group by department;

print(res)
# 输出：<QuerySet [{'num': 2}, {'num': 3}, {'num': 2}]>

跟在annotate前的filter方法表示where条件，跟在annotate后的filter方法表示having条件，如下

# 查询男员工数超过2人的部门名
res=Employee.objects.filter(gender=1).values('department').annotate(male_count=Count("id")).filter(male_count__gt=2).values('department')

print(res) # 输出：<QuerySet [{'department': '技术部'}]>

# 解析：
# 1、跟在annotate前的filter(gender=1) 相当于 where gender = 1，先过滤出所有男员工信息
# 2、values('department').annotate(male_count=Count("id")) 相当于group by department，对过滤出的男员工按照部门分组，然后聚合出每个部门内的男员工数赋值给字段male_count
# 3、跟在annotate后的filter(male_count__gt=2) 相当于 having male_count > 2,会过滤出男员工数超过2人的部门
# 4、最后的values('department')代表从最终的结果中只取部门名

总结：

1、values()在annotate()前表示group by的字段，在后表示取值
1、filter()在annotate()前表示where条件，在后表示having

需要注意的是，如果我们在annotate前没有指定values()，那默认用表中的id字段作为分组依据，而id各不相同，如此分组是没有意义的，如下

res=Employee.objects.annotate(Count('name')) # 每条记录都是一个分组
res=Employee.objects.all().annotate(Count('name')) # 同上

annotate()为调用的QuerySet中每一个对象都生成一个独立的统计值（统计方法用聚合函数）。

总结：

跨表分组查询本质就是将关联表join成一张表，再按单表的思路进行分组查询。　

七、进阶练习(连续跨表)

练习：统计每一本书作者个数

from django.db.models import Avg, Max, Sum, Min, Max, Count
book_list = models.Book.objects.all().annotate(author_num=Count("authors"))
for book in book_list:
     print(book.name)
     print(book.author_num)
book_list = models.Book.objects.all().annotate(author_num=Count("authors")).values('name','author_num')
print(book_list)

练习：统计每一个出版社的最便宜的书

publishList=Publish.objects.annotate(MinPrice=Min("book__price"))
for publish_obj in publishList:
    print(publish_obj.name,publish_obj.MinPrice)

annotate的返回值是querySet，如果不想遍历对象，可以用上valuelist：

queryResult= Publish.objects.annotate(MinPrice=Min("book__price")).values_list("name","MinPrice")
print(queryResult)

练习：统计每一本以py开头的书籍的作者个数：

 queryResult=Book.objects.filter(title__startswith="Py").annotate(num_authors=Count('authors'))

练习：统计不止一个作者的图书：（作者数量大于一）

ret=models.Book.objects.annotate(author_num=Count("authors")).filter(author_num__gt=1).values('name','author_num')
print(ret)

练习：根据一本图书作者数量的多少对查询集 `QuerySet`进行排序:

Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')

练习：查询各个作者出的书的总价格:

ret=models.Author.objects.annotate(sum_price=Sum("book__price")).values("name", "sum_price")
print(ret)

练习：查询每个出版社的名称和书籍个数

ret=models.Publish.objects.all().annotate(c=Count('book__name')).values('name','c')
print(ret)

单表下的分组查询

    '''
    查询每一个部门名称以及对应的员工数
    emp:
    id  name age   salary    dep
    1   alex  12   2000     销售部
    2   egon  22   3000     人事部
    3   wen   22   5000     人事部
    '''
    # select count(id) from emp group by dep
    # 示例一：查询每一个部门的名称，以及平均薪水
    # select dep,Avg(salary) from app01_emp group by dep
    from django.db.models import Avg, Count, Max, Min
    ret=Emp.objects.values('dep').annotate(Avg('salary'))
    # 重新命名
    ret=Emp.objects.values('dep').annotate(avg_salary=Avg('salary'))
    print(ret)
    # ---*******单表分组查询ORM总结：表名.objects.values('group by 的字段').annotate(聚合函数（'统计的字段'）)
    # 示例2 查询每个省份对应的员工数
    ret=Emp.objects.values('province').annotate(Count('id'))
    ret=Emp.objects.values('province').annotate(c=Count('id'))
    print(ret)
    # 补充知识点：
    ret=Emp.objects.all()
    # select * from emp
    ret=Emp.objects.values('name')
    # select name from emp
    # ****单表下，按照id进行分组是没有任何意义的
    ret=Emp.objects.all().annotate(Avg('salary'))
    print(ret)
    # ******多表分组查询
    # 查询每一个出版社出版的书籍个数
    ret=Book.objects.values('publish_id').annotate(Count('nid'))
    print(ret)
    # 查询每个出版社的名称以及出版社书的个数(先join在跨表分组)
    # 正向
    ret=Publish.objects.values('name').annotate(Count('book__name'))
    ret=Publish.objects.values('nid').annotate(c=Count('book__name')).values('name','c')
    print(ret)
    # 反向
    ret=Book.objects.values('publish__name').annotate(Count('name'))
    ret=Book.objects.values('publish__name').annotate(c=Count('name')).values('publish__name','c')
    print(ret)
    # 查询每个作者的名字，以及出版过书籍的最高价格
    ret=Author.objects.values('pk').annotate(c=Max('book__price')).values('name','c')
    print(ret)
    # 跨表查询的模型：每一个后表模型.objects.value('pk').annotate(聚合函数('关联表__统计字段')).values()

    # 查询每一个书籍的名称，以及对应的作者个数
    ret=Book.objects.values('pk').annotate(c=Count('authors__name')).values('name','c')
    print(ret)
    # 统计不止一个作者的图书
    ret=Book.objects.values('pk').annotate(c=Count('authors__name')).filter(c__gt=1).values('name','c')
    print(ret)

posted @ 2019-10-24 19:31 Thousand_Mesh 阅读(214) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

公告

昵称： Thousand_Mesh
园龄： 5年7个月
粉丝： 20
关注： 9

+加关注

2025年3月

日

一

二

三

四

五

六

Thousand_Mesh

Django 多表操作

Django 多表操作

一 表关系回顾

app01_book与app01_publish

app01_author与app01_authordetail

app01_book与app01_author

二 创建模型

三 添加、删除、修改记录

3.1 添加记录

1、多对一：app01_book与app01_publish

2、一对一：app01_author与app01_authordetail

3、多对多：app01_book与app01_author

3.2 删除、修改记录

四 查询记录

4.1 基于对象的跨表查询

1、跨两张表查询

1.1、一对一查询（模型类Author与AuthorDetail）

1.2、一对多查询（模型类Book与Publish）

1.3、多对多查询（模型类Book与Author）

2、连续跨>2张表查询

4.2 基于双下划线的跨表查询

1、跨两张表查询

1.1、一对一查询（模型类Author与AuthorDetail）

1.2、多对一查询（模型类Book与Publish）

1.3、多对多查询（模型类Book与Author）

2、连续跨>2张表查询

4.3 related_name

五、 F与Q查询

F查询

Q查询

六、聚合查询与分组查询

6.1 聚合查询

6.2 分组查询

总结 ：

七、进阶练习(连续跨表)

练习：统计每一本书作者个数

练习：统计每一个出版社的最便宜的书

练习：统计每一本以py开头的书籍的作者个数：

练习：统计不止一个作者的图书：（作者数量大于一）

练习：根据一本图书作者数量的多少对查询集 QuerySet进行排序:

练习：查询各个作者出的书的总价格:

练习：查询每个出版社的名称和书籍个数

单表下的分组查询

公告

搜索

常用链接

合集

随笔分类

随笔档案

相册

阅读排行榜

评论排行榜

推荐排行榜

最新评论

一表关系回顾

二创建模型

三添加、删除、修改记录

四查询记录

总结：

练习：根据一本图书作者数量的多少对查询集 `QuerySet`进行排序: