I've tried replacing INNER_QUERY with "myApp_Instructionssteptranslation", and also leaving it away but this just gives other errors.
So, how come the generated query seems to work correctly when ran apart, but fails when we want to retrieve its results using django? And how can I fix the issue so that it behaves like I want it too?We have a model InstructionsStep, which has a foreign key to a Instructions, which in turn is connected to a Library. A InstructionsStep has a description, but as multiple languages might exist this description is stored in a separate model containing a language code and the description translated in that language.
But for performance reasons, we need to be able to get a queryset of Instructionssteps, where the description is annotated in the default language (which is stored in the Library). To achieve this and to circumvent django's limitations on joins within annotations, we created a custom Aggregate function that retrieves this language. (DefaultInstructionsStepTranslationDescription)
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()
)
)
This generates the following sql-query (the database is a postgres database)
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
Which works correctly when pasted in Postico.
However, running this in 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()
gives the following error:
LINE 1: ...ge" = (MIN(U3."default_language"))) THEN (SELECT INNER_QUER...
^
HINT: Perhaps you meant to reference the column "inner_query.description".
I've tried replacing INNER_QUERY with "myApp_Instructionssteptranslation", and also leaving it away but this just gives other errors.
So, how come the generated query seems to work correctly when ran apart, but fails when we want to retrieve its results using django? And how can I fix the issue so that it behaves like I want it too?
