extra 在django orm中使用复杂的sql语句
extra(select=None, where=None, params=None, tables=None, order_by=None, select_params=None)

有些情况下,Django 的查询语法难以简练地表达复杂的 WHERE 子句。对于这种情况,Django 提供了 extra() QuerySet 修改机制,它能在QuerySet 生成的 SQL 从句中注入新子句。

由于产品差异的原因,这些自定义的查询难以保障在不同的数据库之间兼容(因为你手写 SQL 代码的原因),而且违背了 DRY 原则,所以如非必要,还是尽量避免写 extra。

在 extra 可以指定一个或多个 params 参数,如 select,where 或 tables。所有参数都是可选的,但你至少要使用一个。

   select
select 参数可以让你在 SELECT 从句中添加其他字段信息。它应该是一个字典,存放着属性名到 SQL 从句的映射。

例如:

Entry.objects.extra(select={'is_recent': "pub_date > '2006-01-01'"})

 

结果中每个 Entry 对象都有一个额外的 is_recent 属性,它是一个布尔值,表示 pub_date 是否晚于2006年1月1号。

Django 会直接在 SELECT 中加入对应的 SQL 片断,所以转换后的 SQL 如下:

SELECT blog_entry.*, (pub_date > '2006-01-01')
FROM blog_entry;

 

下面这个例子更复杂一些;它会在每个 Blog 对象中添加一个 entry_count 属性,它会运行一个子查询,得到相关联的 Entry 对象的数量:

Blog.objects.extra(
select={
'entry_count': 'SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id'},
)

 

(在上面这个特例中,我们要了解这个事实,就是 blog_blog 表已经存在于 FROM 从句中。)

翻译成 SQL 如下:

SELECT blog_blog.*, (SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id) AS entry_count
FROM blog_blog;

 

要注意的是,大多数数据库需要在子句两端添加括号,而在 Django 的 select 从句中却无须这样。同样要引起注意的是,在某些数据库中,比如某些 MySQL 版本,是不支持子查询的。

某些时候,你可能想给 extra(select=...) 中的 SQL 语句传递参数,这时就可以使用 select_params 参数。因为 select_params 是一个队列,而 select 属性是一个字典,所以两者在匹配时应正确地一一对应。在这种情况下中,你应该使用 django.utils.datastructures.SortedDict 匹配 select 的值,而不是使用一般的 Python 队列。

例如:

Blog.objects.extra(
select=SortedDict([('a', '%s'), ('b', '%s')]),
select_params=('one', 'two'))

 

在使用 extra() 时要避免在 select 字串含有 "%%s" 子串, 这是因为在 Django 中,处理 select 字串时查找的是 %s 而并非转义后的 % 字符。所以如果对 % 进行了转义,反而得不到正确的结果。

   where / tables
你可以使用 where 参数显示定义 SQL 中的 WHERE 从句,有时也可以运行非显式地连接。你还可以使用 tables 手动地给 SQL FROM 从句添加其他表。

where 和 tables 都接受字符串列表做为参数。所有的 where 参数彼此之间都是 "AND" 关系。

例如:

Entry.objects.extra(where=['id IN (3, 4, 5, 20)'])

 

大致可以翻译为如下的 SQL:

SELECT * FROM blog_entry WHERE id IN (3, 4, 5, 20);

 

在使用 tables 时,如果你指定的表在查询中已出现过,那么要格外小心。当你通过 tables 参数添加其他数据表时,如果这个表已经被包含在查询中,那么 Django 就会认为你想再一次包含这个表。这就导致了一个问题:由于重复出现多次的表会被赋予一个别名,所以除了第一次之外,每个重复的表名都会分别由 Django 分配一个别名。所以,如果你同时使用了 where 参数,在其中用到了某个重复表,却不知它的别名,那么就会导致错误。

一般情况下,你只会添加一个未在查询中出现的新表。但是如果上面所提到的特殊情况发生了,那么可以采用如下措施解决。首先,判断是否有必要要出现重复的表,能否将重复的表去掉。如果这点行不通,就试着把 extra() 调用放在查询结构的起始处,因为首次出现的表名不会被重命名,所以可能能解决问题。如果这也不行,那就查看生成的 SQL 语句,从中找出各个数据库的别名,然后依此重写 where 参数,因为只要你每次都用同样的方式调用查询(queryset),表的别名都不会发生变化。所以你可以直接使用表的别名来构造 where。

   order_by
如果你已通过 extra() 添加了新字段或是数据库,此时若想对新字段进行排序,就可以给 extra() 中的 order_by 参数传递一个排序字符串序列。字符串可以是 model 原生的字段名(与使用普通的 order_by() 方法一样),也可以是 table_name.column_name 这种形式,或者是你在 extra() 的 select 中所定义的字段。

例如:

q = Entry.objects.extra(select={'is_recent': "pub_date > '2006-01-01'"})
q = q.extra(order_by = ['-is_recent'])

 

这段代码按照 is_recent 对记录进行排序,字段值是 True 的排在前面,False 的排在后面。(True 在降序排序时是排在 False 的前面)。

顺便说一下,上面这段代码同时也展示出,可以依你所愿的那样多次调用 extra() 操作(每次添加新的语句结构即可)。