ORM的多表查询详述

ORM的多表查询

ORM最核心与用的最多的地方就是跨表查询了。这里的“跨表查询”分为以下几种：基于对象的跨表查询、基于双下划线的跨表查询、聚合查询、F与Q查询以及分组查询。

下面就为大家详细阐述这几种查询的具体细节及用法。

另外，本文省去了Django与MySQL数据库之间建立连接以及创建表、添加表记录的过程。如果大家有兴趣可以回顾下我之前的两篇文章：

https://www.cnblogs.com/paulwhw/p/9395085.html
https://www.cnblogs.com/paulwhw/p/9405168.html

本文在这两篇文章的基础上进行详述。

基于对象的跨表查询

基于对象的跨表查询最终翻译成我们的SQL语句其实是子查询的语句，也就是说我们利用一个查询的结果作为另外一个查询的条件进行查询。数据库中的一个表记录其实就是筛选出来的“对象”的一个对象————可以利用.操作符操作。

一对多关系的查询

我们在前面建立的Book表与Publish表就是一对多的关系：一本书只能有一个出版社，而同一个出版社可以出版多本书。

查询要点：正向查询按字段；反向查询按表名小写_set.all()。以Book表为基准，由于我们将关联的字段定义在了Book表中，也就是说“关联字段”在Book表中，所以从Book开始查是“正向”，从Publish开始查是“反向”。

这里列举一个正向查询的例子：查询主键为1的书籍的出版社的城市

book_obj = Book.objects.filter(pk=1).first()
ret = book_obj.publish.city
print(ret)

这里再列举一个反向查询的例子，大家可以类比的记忆,查询“苹果出版社”出版过的所有书籍的书名

publish_obj = Publish.objects.filter(name='苹果出版社').first()
book_list = publish.book_set.all()
for book_obj in book_list:
	print(book_obj.title)

多对多关系查询：

我们在前面建立的Book表与Author表，就是“多对多”的关系。

查询要点：正向查询按字段；反向查询按表名小写_set.all()。以Book表为基准，由于我们将关联的字段定义在了Book表中，也就是说“关联字段”在Book表中，所以从Book开始查是“正向”，从Author开始查是“反向”。

这里还是列举一个正向查询的例子，“三国群英”所有的作者及手机号

book_obj = Book.objects.filter(title='三国群英').first()
authors = book_obj.authors.all()
for author_obj in authors:
	name = author_obj.name
	##手机号在“作者详细表”中，而且“作者表”相对于“作者详细表”是正向，关联字段为authordetail
	telephone = author_obj.authordetail.telephone
	print('作者:%s，手机号:%s'%(name,telephone))

下面是一个“反向查询”的例子,查询作者whw出版过的所有书籍的名字

author_obj = Author.objects.filter(name='whw').first()
book_list = author_obj.book_set.all()
for book_obj in book_list:
    print(book_obj.title)

一对一关系的查询

Author表与AuthorDetail表是一对一的关系。

查询要点：正向查询按字段；反向查询按表名小写。以Author表为基准，由于我们将关联的字段定义在了Author表中，也就是说“关联字段”在Author表中，所以从Author开始查是“正向”，从AuthorDetail开始查是“反向”。

正向查询的例子，查询作者whw的电话

author = Author.objects.filter(name='whw').first()
##正向查询按字段
ret = author.authordetail.telephone
print(ret)

反向查询的例子：查询电话是12312312的作者名字

add = AuthorDetail.objects.filter(telephone=12312312).first()
#反向查询按表名小写：
print(add.author.name)

基于双下划线的跨表查询

基于双下划线的跨表查询————最终翻译成SQL语句都是“join查询” 。

这里直接给出查询规则——正向查询按字段，反向查询按表名小写——用来告诉ORM引擎join哪张表。其实本质上就是先join成一张表，再执行“单表查询”。这里还需要注意的一点是：查询中用到的APIvalues()等同于SQL语句中的select；filter()等同于SQL语句中的where。

一对多关系的双下划线查询

这里我们还是以Book与Publish表为例。需要注意的是，正向查询按字段，反向查询按表名小写用来告诉ORM引擎join哪张表。

为了帮助大家更好的理解，下面的每个例子都与我们的SQL语句类比，帮助大家更容易的理解，因为基于双下滑先的跨表查询在实际中用的非常多！

例：查询水浒传这本书的出版社的名字

在SQL中我们是这样实现的:

select publish.name from book inner join publish
on book.publish_id = publish.nid
where book.title='水浒传'

正向查询：

ret = Book.objects.filter(title='水浒传').values('publish__name')
print(ret)

反向查询：

ret = Publish.objects.filter(book__title='水浒传').values('name')

print(ret)

多对多关系的双下划线查询

这里我们还是以Book与Author表为例。需要注意的是，正向查询按字段，反向查询按表名小写用来告诉ORM引擎join哪张表。

这里的例子我们还是用SQL进行类比

例：查询三国群英这本书所有作者的名字

注意这里总共涉及三张表：Book、Author、book_authors。但是第三张表我们是用Django在Book中与Author建立关联生成的表，也就是说：Book查Author是“正向查询”，Author查Book是“反向查询”。

SQL语句实现：

select author.name from book inner join book_authors 
on book.nid = book_author.book_id
inner join author 
on book_authors.author_id = author.nid
where book.title = "三国群英"

正向查询：

ret = Book.objects.filter(title='三国群英').values('authors__name')
print(ret)

反向查询：

ret = Author.objects.filter(book__title='三国群英').values('name')
print(ret)

一对一关系的双下划线查询

一对一的关系相对的很好理解，我们这里直接给出例子，查询whw的手机号

正向查询

ret = Author.objects.filter(name='whw').values('authordetail__telephone')
print(ret)

反向查询

ret = AuthorDetail.objects.filter(author__name='whw').values('telephone')
print(ret)

聚合查询

在做聚合查询之前我们需要先引入下列模块：

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

这里直接给出一个例子大家体会一下它的用法，真的很简单。

例：查询所有书籍的平均价格以及最高的价格

from django.db.models import Avg,Max
ret = Book.objects.all().aggregate(avg_price=Avg('price'),max_price=Max('price'))
print(ret)

F与Q查询

F查询

在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？

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

我们在使用前应先引入：

from django.db.models import F

比如我们想查询content_num大于read_num的书籍的名字：

ret = Book.objects.filter(content_num__gt=F('read_num')).values('title')
print(ret)

我们还可以将每个书籍的价格加10元：

Book.objects.all().update(price=F('price')+10)

Q查询

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

当然使用前必须引入：

from django.db.models import Q

这里直接给出三个例子：

1、查找书名以“三国”开头或者价格等于100的书籍名称

ret = Book.objects.filter(Q(title__startswith='三国')|Q(price=100)).values('title')
print(ret)

2、查找书名不以“三国”开头的书籍名称

ret = Book.objects.filter(~Q(title__startswith='三国')).values('title')
print(ret)

Q查询与键值对的关系：先写Q再写键值对，而且是“且”的关系：

ret = Book.objects.filter(~Q(title__startswith='三国'),title__startswith='水').values('title')
print(ret)

分组查询

分组查询的思想跟我们SQL中group by的思路是一模一样的，也就是说，我们先按照某个字段为数据进行分组，然后再进行进一步的查询。

ORM的分组查询包括：单表下的分组查询与多表下的分组查询。

单表下的分组查询

准备工作：我们新建一张员工表emp，包含的字段有——：id、name、age、salary、dep(部门名)、province(省份)。models.py文件的类这样写：

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)

单表下的分组查询语法：

单表模型.objects.values('group by的字段').annotate(聚合函数('统计字段'))

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

from django.db.models import Avg
ret = Emp.objects.values('dep').annotate(Avg('salary'))
print(ret)

另外还需要注意的是：在单表分组下，按着主键进行分组是没有任何意义的！这与我们SQL中是一样的道理。

多表下的分组查询

单表下的分组查询语法：

每一个后表模型.objects.values('pk').annotate(聚合函数('关联表__统计字段')).values('表模型的所有字段以及统计字段')

注意以哪张表中的字段分组，哪一张表就是“后表”。

为了方便大家理解，下面的例子还是用SQL语句进行类比

例：查询每一个作者的名字以及出版过的书籍的最高价格

SQL：

select author.name,Max(book.price) from book inner join book_authors
on book.nid = book_authors.book_id
inner join author 
on autohr.nid = book_authors.author_id
group by author.nid

annotate方法：注意主键可以用 pk 表示；Author找Book是“反向查询”按表名小写

ret = Author.objects.values('pk').annotate(max_price=Max('book__price')).values('name','max_price')
print(ret)