09-SQL语句的生成-概览
orm 与 SQL
orm 的最重要的作用是帮助我们从繁重的 sql 书写中解放出来,依据灵活的查询的条件动态生成 sql。毕竟数据库最终查询数据也是只认 sql。所以搞明白 sql 的生成尤为重要。理解了 sql 的生成,就可以在使用中注意到很多细节,有时候 orm 的查询结果可能并不是我们所想的那样。
举个我们公司遇到的例子。
我们公司一共有5个分公司,每个公司都有自己的销售。而我们的客户会由销售去对接,并且一个客户只能属于一个销售,客户与销售之间的关系是多对一,并且客户所属的销售可以变更,变更的时候,新的销售可以不是原来的销售所属的分公司。假设客户A的第一个销售是甲,那么我们称之为客户A是销售甲开发成功的。如果客户A后面变更了对接的销售,为乙,我们不能说客户A是销售乙开发成功的。这个开发成功是有次序问题的。
客户表 Partner
销售表 Saler
客户销售关系表 SalerPartner
数据库:Postgresql
现在需要做一个统计:统计每一个分公司开发成功的客户数量。还有个限定条件,前端页面上针对每一个分公司都有一个版块儿,因此需要按照分公司单独统计5次。
如果使用 orm 大概率是这么写
base_qs = SalerPartner.objects.order_by('create_time').distinct(partner_id)
res = base_qs.filter(saler__company='深圳').count()
res = base_qs.filter(saler__company='北京').count()
res = base_qs.filter(saler__company='上海').count()
res = base_qs.filter(saler__company='常州').count()
res = base_qs.filter(saler__company='廊坊').count()
但是这么写一定会有问题,假如客户一共有 500 个,那么五个分公司的所有开发成功的客户数量也一定是500,可是上面的写法五个数量加起来极大概率是大于 500 的。
我们以深圳为例,上面的 orm 翻译成 sql 会是什么样子
select count(*)
from(
select distinct (on a.id) a.*
from SalerPartner a
inner join Saler b on a.saler_id=b.id
where b.company='深圳'
order by a.create_time
) c
我们需要知道 SQL 的执行顺序是 where,join, order_by, select, limit。更为详细的请自行百度,这几个关键字的执行顺序是这样的。因此上面的 sql 是先过滤出深圳的销售与客户关系记录,在按照记录创建时间排序,然后去重,最后计算数据条数。
假如一个客户第一销售属于深圳,第二销售属于北京,那么在对深圳和北京的分别统计中肯定都会出现这个客户,就会导致五个分公司的客户总数加起来超过整个系统中的客户总数。这个不符合统计要求。
如果使用 sql,应该如何避免这种情况呢?
select count(*)
from SalerPartner
where id in (
select distinct on (a.partner_id) a.id
from SalerPartner a
inner join Saler b on a.saler_id=b.id
order by a.create_time
)
and b.company='深圳'
orm 则是这样:
qs_ids = SalerPartner.objects.order_by('create_time').distinct(partner_id).values_list('id', flat=True)
qs_base = SalerPartner.objects.filter(pk__in=qs_ids)
res = base_qs.filter(saler__company='深圳').count()
res = base_qs.filter(saler__company='北京').count()
res = base_qs.filter(saler__company='上海').count()
res = base_qs.filter(saler__company='常州').count()
res = base_qs.filter(saler__company='廊坊').count()
这样的 orm 会生成一个子查询,最终结果符合要求。
其实对于 orm,还有很多使用上的细节,只有足够了解他,才能正确使用他。当然,看源码,学习框架的思想和原理本身就是一间有趣的事,也是快速提高开发能力的一种捷径。
上一篇博客讲述到了 as_sql 这个关键方法,他是如何将 Query 对象翻译成 SQL 的。本篇文章将会对其主要流程做一个概述,然后通过这篇概述文章发散去弄明白整个 orm 的细节。由整体到局部,这是学习编程的重要思想。
as_sql 的返回
在调用 as_sql 的方法处,可以明确的确认,他的返回值是一个 sql 和一个 参数元组。
我们在 as_sql 方法返回之前,打上断点,重新调试,可以发现如下的截图内容
result 是一个列表,他存储了一个 sql 的每一个关键字及其对应的内容。其实 sql 的每一个部分都是很清晰的。
select 确定输出字段
from 确定获取表数据的主要字段
join on 确定关联的表, 以及关联字段
where 负责过滤数据, 或者是限制数据
limit 负责输出数据的条数
group 确定分组字段,此时 select 衔接的字段要么是
having 确定对聚合之后的数据进行过滤
一条完整的 sql 其实就这么些,而子查询其实是 where 的扩展,我们可以先搞明白单层查询是怎么回事。至于 SQL 校验完全可以交给数据库来做,框架本身来参与校验,是多此一举的事情。
再看 result 这个列表完全就是一条sql 的顺序拆分,然后简单的用空格拼接。浏览整个 as_sql 的源码,也可以发现,其实 as_sql 就是如此做的,基于 Query 示例,计算每一部分,放入 result 列表,最后拼接。所以接下来的分析也是基于此来做。
测试代码
这个测试代码将会触发 select,where,group by, having, limit, join, from 等关键字
断点
断点主要打在 as_sql 即将返回的时候,使用的 QuerySet 依旧是我们重写的 QuerySet2,这是为了方便调试。
关键字对应
可以看到每一个关键字在调试器中都有对应。下面我们将会对每一个部分进行逐一的讲解,并且是按照 从 select 到 limit 的顺序
select
select概览
select 后面衔接的查询显示字段来自两处。
【1】这里怎么触发我尚不清楚,后续如果搞清楚了再来回补这一段
【2】大概率是最终的 values 方法内传递的元素,extra_select 目前还不清楚。依据别名对查询显示字段名进行 as 修饰,从而组合查询语句。
【3】select 是一个列表,每一个列表都是一个三元组,其中第一个元素是一个 Col 的列对象,他描述了某个字段的具体的位置,api[包名].Province[Model].name[字段],这个元素被舍弃了,因为 sql 语句中不需要什么对象。第二个元素是一个二元组,二元组两个元素分别是对应的查询语句和对应的参数值[select 部分没有用到],第三个元素是字段别名,如果是Model中已经定义的字段名肯定没有别名,如果使用了 annotate 设置了字段别名,则第三个元素肯定有值。例如这里的 citys.annotate 不光可以对新的数据列进行命名,也可以对已有的字段进行重命名。
self.select 怎么来的
这里我们重点查看 self.select,他是怎么来的。self 对应的类是当前的 django.db.models.sql.SQLCompiler
。在初始化方法内,他被设为默认的 None, 因此他一定是在哪里被重新赋值了,只需要在文件内搜索 self.select
,一个一个排查就可以知道了。
最终我们发现了这样一条调用轨迹,get_select,应该就是他了。
get_select 返回的数据是 ret,这个对应的就是前面提到的 self.select, 而 ret 来自于 select,因此我们只需要知道 get_select 方法内的 select 元素是怎么生成的就好了。
select 列表的元素主要来自【1,2,3】三大块儿
【1】跟 QuerySet2.extra_select 字典有关,这里为空,我也没弄清楚这个是什么东西,后续搞清楚了再回补这篇文章
【2】这里,QuerySet2 的默认列和显示查询列不能都有值,否则会报错,这里我们猜想默认列是当我们没有调用 values 方法或者 values 方法参数列表为空的时候,指向的所有的列,如果是聚合查询则是聚合字段以及聚合之后的字段,如果不是聚合查询则是所有的表字段加别名字段。
【3】是别名列,别名列又包括聚合之后的列。
我们不能详细展开 default_cols 是什么时候修改了默认值,这样整篇文章结构就不清晰了,我们还是继续讨论 self.query.select
和 self.query.annotation_select
, 这两个属性是何时修改和添加的。因为这里我们用到了他们。
self.query.select
这里的 self 指的是 SQLCompiler, query 当前是我重写的 Query2,也就是说 Query 被写入了 SQLCompiler 编译器中去了,这个是什么时候写进去的?
圈出来的部分返回之前分明是做一个初始化动作,并将 self,也就是当前的 Query2 的实例作为参数传递进去了。所以 Query 是在 SQLCompiler 初始化的时候就会传递进去。并且 SQLCompiler 的初始化动作只发生在 QuerySet 想要查询数据的时候。
这里总结一下 QuerySet Query ValuesIterable(BaseIterable) SQLCompiler 之间的关系
QuerySet 可以进行各种 orm 操作,每次进行新的 orm 操作就会在原来的基础上进行克隆,制造一个新的 QuerySet,克隆是深度克隆,包括自己的属性 Query 实例也被克隆了一份,然后将新调用的 orm 方法对应的改变写入自己的属性 Query 实例中。
Query 将各种 orm 操作分门别类放入对应的集合之中,方便 SQLCompiler 进行编译成 SQL 语句,Query 依据配置的数据库别名,获取对应的 SQLCompiler 类,其实每一种数据库操作的编译器都叫做 SQLCompiler,只是他们在不同的路径下。
ValuesIterable(BaseIterable),其实主要说的是 BaseIterable,QuerySet 有一个属性叫做 _iterable_class,每次调用 orm 方法都有可能改写 _iterable_class 的指向,BaseIterable 初始化的时候需要传递当前的 QuerySet 实例,然后在迭代方法里,直接从 QuerySet 获取 Query 实例,Query 实例里又可以直接获取 SQLCompiler
SQLCompiler 必须拿到 Query 的实例,才能翻译成 SQL,SQLCompiler 翻译出 SQL,并且执行,返回给了 BaseIterable,BaseIterable 又返回给了 QuerySet,QuerySet 会先缓存一份,然后呈现给我们。
读者可以自己画一份关系图。这些是我的总结,希望读者带着这份结构去看源码。
回到 self.query.select
这个 query 明显指的是 Query 类,此处的 select 已经即将向 SQL 中写 SELECT 后面的字段,我们先在测试代码中调试,看是哪一步向 Query 的实例中的 select 集合中写入了数据
经过调试发现,qs4 就已经向 query 中的 select 集合中写入了查询字段,读者可以自己调试,这里就不方便截图。所以应该重点关注 values。接下来就采用夹逼法锁定写入代码。
夹逼法【其实前面已经演示过了】:
一行一行得调试源码,如果某一行突然发生了数据变化,就跳到这一行代码调用里,然后重新调试,从新的代码块开始一行一行得调试,不断锁定。
Query.set_select 方法向 Query 里面的 select 里面添加了内容,至于 add_fields 方法内的其他内容,暂时还不用管。
总结:values 方法会将 Query 的 select 属性直接替换为一个新的元组,这个元组中的值完全来自于 values[最终会转换为一个个的 Col 对象],同时需要注意的是,一旦调用了 values,那么 default_cols 就会被置为 False。所以我们还可以知道,这里使用的测试代码中,最后一个 values 方法执行后 Query.select 集合中有两个元素,其实是一次行写入的,而不是逐步增加的。
FROM
可以看到当 combinator 没有值的时候才会走我们即将要分析的这部分代码那么这个 combinator 到底是个什么呢?最主要的是他的值什么时候给上的。
前面分析了很多代码,我们知道每个 orm 方法最后都会返回一个新的QuerySet,新的 QuerySet 里面引用了新的 Query。所以 combinator 的赋值,存在以下几种方式 clone.query.combinator = xxx,self.combinator = xxx, 不可能存在 self.query.combinator = xxx 这种形式的,这一点需要注意,因为 orm 的方法永远不会操作当前的属性,他只会操作一个即将返回的 QuerySet 的属性。通过搜索我们得知。
【1】是我搜索到的唯一的修改 combinator 的源码,而【2】是所有调用了该方法的地方,可以发现 combinator 都是集合之间的操作,两个集合之间的交并差,并反操作,这个时候自然不能走普通的编译操作。
好了,回到 from
from 后面添加的元素主要来自于 get_from_clause 方法的返回值,并且 get_from_clause 的返回值是列表。get_from_clause 的函数主体中主要分为两部分,上半部分用的最多,使我们的常规查询,下部分是额外查询 QuerySet.extra 方法。到目前为止我还没用到过呢,我们主要探讨上半部分的源码。
【1】的具体作用我到现在不知道是啥,以后遇到了再写一篇博客,【2】是将 Query.alias_map 中的每一个对象取出来,然后通过 SQLCompiler.compile 方法编译成对应的 sql 部分。alias_map 中的元素全部是表,其中objects 的起点表被封装成一个 BaseTable 对象,而后续所有跟他相关联的表,主要是外键产生的关联,都是 Join 对象。后续将会对这两个类展开详细的讲解。【3】就是将 BaseTable 对象或者 Join 对象编译成 对应的 sql 部分。在右侧的调试器里,可以清晰看到 alias_map 就是一个封装的表对象,注意跟 Model 类的区分。
所以接下来的重点是分析 SQLCompiler.compile 方法 和 BaseTable 于 Join 类。
【1】一般情况下是不会触发的,右侧可以看到 vender 表示的是数据库的名字, as_mysql,这个是专属sql翻译,属于数据库的方言。【2】是通用的翻译。因此需要看每一个传进来的 node 的 as_sql 干了什么,因此接下来重点去看 BaseTable 和 Join 类
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库