生成的Django queryset可以工作，但在Django中运行它失败

Question

我试过用“myApp\u指令steptransation”替换内部查询，也不使用它，但这只会导致其他错误。你知道吗

那么，为什么生成的查询在分开运行时似乎能正常工作，而在使用django检索其结果时却失败了呢？ 我怎样才能解决这个问题，让它表现得像我想要的一样？我们有一个模型instructionstep，它有一个指向指令的外键，而指令又连接到一个库。指令步骤有一个描述，但由于可能存在多种语言，此描述存储在一个单独的模型中，该模型包含一个语言代码和用该语言翻译的描述。你知道吗

但是出于性能原因，我们需要能够获得instructionsteps的queryset，其中描述是用默认语言（存储在库中）注释的。为了实现这一点，并绕过django对注释内连接的限制，我们创建了一个检索这种语言的自定义聚合函数。（默认指令StepTranslationDescription）

class InstructionsStepTranslationQuerySet(models.query.QuerySet):
    def language(self, language):
        class DefaultInstructionsStepTranslationDescription(Aggregate):

            template = '''
                        (%(function)s %(distinct)s INNER_QUERY."%(expressions)s" FROM (
                            SELECT "myApp_Instructionssteptranslation"."description" AS "description", 
                                    MIN("myUser_library"."default_language") AS "default_language"
                            FROM "myApp_Instructionssteptranslation" 
                                    INNER JOIN "myApp_Instructionsstep" A_ST ON ("myApp_Instructionssteptranslation"."Instructions_step_id" = A_ST."id") 
                                    INNER JOIN "myApp_Instructions" ON (A_ST."Instructions_id" = "myApp_Instructions"."id") 
                                    LEFT OUTER JOIN "myUser_library" ON ("myApp_Instructions"."library_id" = "myUser_library"."id") 
                           WHERE "myApp_Instructionssteptranslation"."Instructions_step_id" = "myApp_Instructionsstep"."id"
                           and "myApp_Instructionssteptranslation"."language" = default_language 
                           GROUP BY "myApp_Instructionssteptranslation"."id" 
                      ) AS INNER_QUERY 
                      LIMIT 1
                      '''
            function = 'SELECT'

            def __init__(self, expression='', **extra):
                super(DefaultInstructionsStepTranslationDescription, self).__init__(
                    expression,
                    distinct='',
                    output_field=CharField(),
                    **extra
                )

        return self.annotate(
            t_description=
            Case(
                When(id__in = InstructionsStepTranslation.objects\
                                .annotate( default_language = Min(F("Instructions_step__Instructions__library__default_language")))\
                                .filter( language=F("default_language") )\
                                .values_list("Instructions_step_id"),
                     then=DefaultInstructionsStepTranslationDescription(Value("description"))
                ),
                default=Value("error"),
                output_field=CharField()
            )
        )

这将生成以下sql查询（数据库是postgres数据库）

    SELECT "myApp_Instructionsstep"."id",
           "myApp_Instructionsstep"."original_id",
           "myApp_Instructionsstep"."number",
           "myApp_Instructionsstep"."Instructions_id",
           "myApp_Instructionsstep"."ccp",
           CASE
               WHEN "myApp_Instructionsstep"."id" IN
                      (SELECT U0."Instructions_step_id"
                       FROM "myApp_Instructionssteptranslation" U0
                       INNER JOIN "myApp_Instructionsstep" U1 ON (U0."Instructions_step_id" = U1."id")
                       INNER JOIN "myApp_Instructions" U2 ON (U1."Instructions_id" = U2."id")
                       LEFT OUTER JOIN "myUser_library" U3 ON (U2."library_id" = U3."id")
                       GROUP BY U0."id"
                       HAVING U0."language" = (MIN(U3."default_language"))) THEN
                      (SELECT INNER_QUERY."description"
                       FROM
                         (SELECT "myApp_Instructionssteptranslation"."description" AS "description",
                                 MIN("myUser_library"."default_language") AS "default_language"
                          FROM "myApp_Instructionssteptranslation"
                          INNER JOIN "myApp_Instructionsstep" A_ST ON ("myApp_Instructionssteptranslation"."Instructions_step_id" = A_ST."id")
                          INNER JOIN "myApp_Instructions" ON (A_ST."Instructions_id" = "myApp_Instructions"."id")
                          LEFT OUTER JOIN "myUser_library" ON ("myApp_Instructions"."library_id" = "myUser_library"."id")
                          WHERE "myApp_Instructionssteptranslation"."Instructions_step_id" = "myApp_Instructionsstep"."id"
                            and "myApp_Instructionssteptranslation"."language" = default_language
                          GROUP BY "myApp_Instructionssteptranslation"."id") AS INNER_QUERY
                       LIMIT 1)
               ELSE 'error'
           END AS "t_description"
    FROM "myApp_Instructionsstep"
    WHERE "myApp_Instructionsstep"."id" = 438
    GROUP BY "myApp_Instructionsstep"."id"
    ORDER BY "myApp_Instructionsstep"."number" ASC

粘贴在Postico中时正确工作。你知道吗

但是，在django运行这个

step_id = 438
# InstructionsStep.objectsobjects is overrided with a custom manager that uses the above defined custon queryset
step_queryset = InstructionsStep.objects.language('en').filter(id=step_id) 
retrieved_steps = step_queryset.all()

出现以下错误：

LINE 1: ...ge" = (MIN(U3."default_language"))) THEN (SELECT  INNER_QUER...
                                                         ^
HINT:  Perhaps you meant to reference the column "inner_query.description".

我试过用“myApp\u指令steptransation”替换内部查询，也不使用它，但这只会导致其他错误。你知道吗

那么，为什么生成的查询在分开运行时似乎能正常工作，而在使用django检索其结果时却失败了呢？ 我怎样才能解决这个问题，让它表现得像我想要的一样？你知道吗

Answer 1

同时，我发现带有.query属性的打印查询与实际执行的查询不同。你知道吗

在本例中，它打印了SELECT INNER_QUERY."description"，但执行了SELECT INNER_QUERY."'description'"。添加单引号是因为给InstructionsStepTranslationQuerySet的值（“description”）表达式

最后我通过传递id字段（F("id")）并使用它来代替A_ST."id"解决了问题。（遗憾的是，这是必要的，因为聚合不允许传递空表达式）