[Django ORM 高级操作] Django 使用 union 合并不同模型(Model) 的查询集(QuerySet)

Django 开发中有时候会遇到这样的需求：查询到不同模型(Model) 的查询集(QuerySet)，需要将其合并成一个查询集，甚至还希望能够对合并后的查询集排序，以便在模板中循环展示。

sorted

>>> import itertools
>>> qs1 = Post.objects.all()
>>> qs2 = Material.objects.all()
>>> qs = itertools.chain(qs1, sq2)
>>> sorted(qs, key=lambda o: o.created_time, reverse=True)

union

QuerySet 的 union 方法

unionUNIONPostMaterialPostMaterialpub_date

frpm django.db import models

class Post(modes.Model):
    title = models.CharField(max_length=255)
    body = models.TextField()
    pub_date = models.DateTimeField()
    pinned = models.BooleanField(default=False)

    class Meta:
        ordering = ['-pinned', '-pub_date']

class Material(models.Model):
    title = models.CharField(max_length=255)
    body = models.TextField()
    pub_date = models.DateTimeField()

    class Meta:
        ordering = ['-pub_date']

MaterialPostpinnedpinned

def get_index_entry_queryset():
    post_qs = Post.objects.all().order_by().annotate(
        type=Value('p', output_field=CharField(max_length=1)),
        entry_pinned=F('pinned'))
    post_qs = post_qs.values_list(
        'title','pub_date','entry_pinned','type'
    )

    material_qs = Material.objects.all().order_by().annotate(
        type=Value('m', output_field=CharField(max_length=1)),
        entry_pinned=Value(False, BooleanField()))
    material_qs = material_qs.values_list(
        'title','pub_date','entry_pinned','type'
    )

    entry_qs = post_qs.union(material_qs)
    entry_qs = entry_qs.order_by('-entry_pinned', '-pub_date')
    return entry_qs

annotatetypeMaterialpinnedannotateentry_pinnedFalsePostpinnedannotatepinned

entry_qsquery

SELECT "blog_post"."title", "blog_post"."pub_date", 'p' AS "type", "blog_post"."pinned" AS "entry_pinned"
FROM "blog_post"
UNION
SELECT "courses_material"."title", "courses_material"."pub_date", 'm' AS "type", False AS "entry_pinned"
FROM "courses_material"
ORDER BY (4) DESC, (2) DESC

数据库查询结果如下：

title	pub_date	type	entry_pinned
Markdown 测试	2019-09-23 15:35:47.898271	p	1
test	2019-09-15 13:13:00	p	0
分类、归档和标签页	2019-09-07 01:41:00	m	0
页面侧边栏：使用自定义模板标签	2019-08-29 23:49:00	m	0

UNIONunionall=TrueUNION ALL

注意事项

UNIONunion

• select 的字段类型必须匹配（字段名可以不同，但排列顺序要一致）。例如 field1 和 field 2 都是整数类型，select field1 和 select field 可以进行 union 操作，当引用时，以第一个 QuerySet 中的字段名进行引用。
• 组合后的查询集，很多方法将不可用。

不过在实际使用过程中，发现还用很多的未提及的限制需要小心翼翼地处理。

例如看到示例中的这两句代码：

post_qs = Post.objects.all().order_by().annotate(
        type=Value('p', output_field=CharField(max_length=1)),
        entry_pinned=F('pinned'))
material_qs = Material.objects.all().order_by().annotate(
        type=Value('m', output_field=CharField(max_length=1)),
        entry_pinned=Value(False, BooleanField()))

order_by()Metaordering

django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of compound statements.

annotatePostpinnedpinnedMaterial

有的同学可能想这样做：

post_qs = Post.objects.all().annotate(
    type=Value('p', output_field=CharField(max_length=1)))
post_qs = post_qs.values_list(
        'title','pub_date','pinned','type'
    )

material_qs = Material.objects.all().annotate(
    type=Value('m', output_field=CharField(max_length=1)),
    pinned=Value(False, BooleanField()))
material_qs = material_qs.values_list(
        'title','pub_date','pinned','type'
    )

Postvalues_listvalues_list

SELECT "blog_post"."title", "blog_post"."pub_date", "blog_post"."pinned", 'p' AS "type"
FROM "blog_post"
UNION
SELECT "courses_material"."title", "courses_material"."pub_date", 'm' AS "type", False AS "pinned"
FROM "courses_material"
ORDER BY (4) DESC, (2) DESC

pinnedannotateannotate

material_qs = Material.objects.all().annotate(
    type=Value('m', output_field=CharField(max_length=1)),
    pinned=Value(False, BooleanField()))

typepinnedUNIONtypepinned

annotate

post_qs = Post.objects.all().annotate(
    type=Value('p', output_field=CharField(max_length=1)))
post_qs = post_qs.values_list(
        'title','pub_date','pinned','type'
    )

material_qs = Material.objects.all().annotate(
    pinned=Value(False, BooleanField())).annotate(
    type=Value('m', output_field=CharField(max_length=1)))
material_qs = material_qs.values_list(
        'title','pub_date','pinned','type'
    )

material_qsannotatepinnedtypepinned

ORDER BYnull_firstnulls_last

entry_qs = entry_qs.order_by(F('type').desc(), '-pub_date')
entry_qs = entry_qs.order_by('-pinned', F('pub_date').desc(nulls_last=True))

将得到如下错误：

django.db.utils.DatabaseError: ORDER BY term does not match any column in the result set.

然而对于实际的 SQL 语句，使用 as 设置的别名字段是可以进行排序的：

SELECT "blog_post"."title", "blog_post"."pub_date", "blog_post"."pinned", 'p' AS "type"
FROM "blog_post"
UNION
SELECT "courses_material"."title", "courses_material"."pub_date", False AS "pinned", 'm' AS "type"
FROM "courses_material"
ORDER BY type DESC, pub_date DESC

直接执行这条查询语句可以得到正确的查询结果，不知道为何 django 会报错。

总结

unionUNION

annotate
annotate

-- EOF --