Django中`Annotate`+`values`+`union`结果不正确

Incorrect results with `annotate` + `values` + `union` in Django(Django中`Annotate`+`values`+`union`结果不正确)

本文介绍了Django中`Annotate`+`values`+`union`结果不正确的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

跳转到编辑以查看更多真实代码示例,更改查询顺序后不起作用

以下是我的模特:

class ModelA(models.Model):
    field_1a = models.CharField(max_length=32)
    field_2a = models.CharField(max_length=32)


class ModelB(models.Model):
    field_1b = models.CharField(max_length=32)
    field_2b = models.CharField(max_length=32)

现在,每个创建2个实例:

ModelA.objects.create(field_1a="1a1", field_2a="1a2")
ModelA.objects.create(field_1a="2a1", field_2a="2a2")
ModelB.objects.create(field_1b="1b1", field_2b="1b2")
ModelB.objects.create(field_1b="2b1", field_2b="2b2")

如果我只查询一个带有注释的模型,我会得到类似的结果:

>>> ModelA.objects.all().annotate(field1=F("field_1a"), field2=F("field_2a")).values("field1", "field2")
[{"field1": "1a1", "field2": "1a2"}, {"field1": "2a1", "field2": "2a2"}]

这是正确的行为。当我想要合并这两个模型时,问题开始了:

# model A first, with annotate
query = ModelA.objects.all().annotate(field1=F("field_1a"), field2=F("field_2a"))
# now union with model B, also annotated
query = query.union(ModelB.objects.all().annotate(field1=F("field_1b"), field2=F("field_2b")))
# get only field1 and field2
query = query.values("field1", "field2")

# the results are skewed:
assert list(query) == [
    {"field1": 1, "field2": "1a1"},
    {"field1": 1, "field2": "1b1"},
    {"field1": 2, "field2": "2a1"},
    {"field1": 2, "field2": "2b1"},
]

断言正确通过,这意味着结果是错误的。values()似乎与变量名不匹配,它只是像在元组上一样迭代对象。field1的值实际上是对象的ID,field2的值是field1

在如此简单的模型中修复这一点非常容易,但我的实际模型非常复杂,并且它们具有不同数量的字段。如何正确合并它们?

编辑

下面您可以找到一个无论union()values()的顺序都失败的扩展示例-模型现在稍微大了一些,似乎不同的字段计数以某种方式混淆了Django:

# models

class ModelA(models.Model):
    field_1a = models.CharField(max_length=32)
    field_1aa = models.CharField(max_length=32, null=True)
    field_1aaa = models.CharField(max_length=32, null=True)
    field_2a = models.CharField(max_length=32)
    extra_a = models.CharField(max_length=32)


class ModelB(models.Model):
    extra = models.CharField(max_length=32)
    field_1b = models.CharField(max_length=32)
    field_2b = models.CharField(max_length=32)
# test

ModelA.objects.create(field_1a="1a1", field_2a="1a2", extra_a="1extra")
    ModelA.objects.create(field_1a="2a1", field_2a="2a2", extra_a="2extra")
    ModelB.objects.create(field_1b="1b1", field_2b="1b2", extra="3extra")
    ModelB.objects.create(field_1b="2b1", field_2b="2b2", extra="4extra")

    values = ("field1", "field2", "extra")

    query = (
        ModelA.objects.all()
        .annotate(
            field1=F("field_1a"), field2=F("field_2a"), extra=F("extra_a")
        )
        .values(*values)
    )
    query = query.union(
        ModelB.objects.all()
        .annotate(field1=F("field_1b"), field2=F("field_2b"))
        .values(*values)
    )
# outcome

assert list(query) == [
        {"field1": "1a1", "field2": "1a2", "extra": "1extra"},
        {"field1": "2a1", "field2": "2a2", "extra": "2extra"},
        {"field1": "3extra", "field2": "1b1", "extra": "1b2"},
        {"field1": "4extra", "field2": "2b1", "extra": "2b2"},
    ]

推荐答案

经过一些调试和源代码,我知道为什么会发生这种情况。我要做的是尝试解释为什么执行annotate+values会导致显示id,以及上面两种情况的区别。

为简单起见,我还将为每个语句编写可能产生的SQL查询。

1.annotate首先对联合查询获取values

qs1 = ModelA.objects.all().annotate(field1=F("field_1a"), field2=F("field_2a"))

编写这样的代码时,Django将获得所有字段+带注释的字段,因此生成的SQL查询如下所示:

select id, field_1a, field_2a, field_1a as field1, field_2a as field2 from ModelA

因此,如果我们有query,这是

的结果
qs = qs1.union(qs2)

Django的结果SQL如下所示:

(select id, field_1a, field_2a, field_1a as field1, field_2a as field2 from ModelA)
UNION
(select id, field_1b, field_2b, field_1b as field1, field_2b as field2 from ModelB)

让我们更深入地了解该SQL是如何生成的。当我们执行union时,combinatorcombined_queries被设置在qs.query上,结果SQL由combining the sql的单个查询生成。所以,总而言之:

qs.sql == qs1.sql UNION qs2.sql # in abstract sense
当我们执行qs.values('field1', 'field2')时,编译器中的col_count被设置为2,这是字段数。如您所见,上面的联合查询返回5列,但在编译器的最终返回中,结果中的每一行都是sliced usingcol_count。现在,这个只有2列的results被传递回ValuesIterable,其中maps所选字段中的每个名称都带有结果列。这就是导致错误结果的原因。

2.annotate+values,然后执行union

现在,让我们看看当annotate直接与values一起使用时会发生什么情况

qs1 = ModelA.objects.all().annotate(field1=F("field_1a"), field2=F("field_2a")).values('field1', 'field2')

生成的SQL为:

select field_1a as field1, field_2a as field2 from ModelA

现在,当我们进行联合时:

qs = qs1.union(qs2)

SQL为:

(select field_1a as field1, field_2a as field2 from ModelA)
UNION
(select field_1b as field1, field_2b as field2 from ModelB)

现在,当qs.values('field1', 'field2')执行时,联合查询返回的列数为2列,与col_count相同,为2,并且每个字段与产生预期结果的单个列匹配。


3.不同的字段批注计数和字段排序

在OP中,有一种情况是,在union之前使用.values都不能产生正确的结果。原因是ModelB中没有extra字段的注释。

那么,让我们来看看为每个模型生成的查询:

ModelA.objects.all()
        .annotate(
            field1=F("field_1a"), field2=F("field_2a"), extra=F("extra_a")
        )
        .values(*values)

SQL变为:

select field_1a as field1, field_2a as field2, extra_a as extra from ModelA

对于模型B:

ModelB.objects.all()
        .annotate(field1=F("field_1b"), field2=F("field_2b"))
        .values(*values)

SQL:

select extra, field_1b as field1, field_2b as field2 from ModelB

和工会是:

(select field_1a as field1, field_2a as field2, extra_a as extra from ModelA)
UNION
(select extra, field_1b as field1, field_2b as field2 from ModelB)
由于带注释的字段列在实际数据库字段之后,ModelBextraModelBfield1混合在一起。为了确保获得正确的结果,请确保生成的SQL中的字段顺序始终正确-带或不带注释。在这种情况下,我建议在ModelB上也注释extra

这篇关于Django中`Annotate`+`values`+`union`结果不正确的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本文标题为:Django中`Annotate`+`values`+`union`结果不正确

基础教程推荐