0

I need to annotate a queryset using a custom function agr_suitable_code on a model field.

which looks like this

def strip_accents(s):
    return ''.join(c for c in unicodedata.normalize('NFD', s)
                   if unicodedata.category(c) != 'Mn')


def agr_suitable_code(on_distro_code):
    upper_str = on_distro_code.upper()
    return strip_accents(upper_str)

nothing special here, the function takes a string and returns it with some changes. Then I try to annotate with it as the following query cannot be achieved without this agr_like_code annotated field.

        related_params[distro.id] = table.objects.filter(
            ProductId__in=[map["ProductId"] for map in related_maps_by_distro.values("ProductId")]
        ).annotate(
            agr_like_code=Func(F("DistributionParameterId__Code"), function=agr_suitable_code),
         prod_to_param_exists=
            Exists(AgrDistributionProductParameter.objects.filter(ProductId=context['product'],
                                                                  DistributionParameterId__Code=OuterRef(
                                                                      "agr_like_code")).values('id')),
            has_agr_param=Case(
                When(Q(agr_like_code__in=agr_parameter_codes)
                     & Q(DistributionParameterId__Code__in=agr_param_map_codes_distro)
                     , then=2),
                When(Q(agr_like_code__in=agr_parameter_codes), then=1),
                default=0,
                output_field=models.IntegerField(),
etc ..

I have tried to formulate this line agr_like_code=Func(F("DistributionParameterId__Code"), function=agr_suitable_code) in many ways but with no success.

The current error I'm receiving is

django.db.utils.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '<'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '<'. (102); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '<'. (102); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '<'. (102); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

I know about Django documentation suggesting writing custom SQL query like

class ConcatPair(Func):
    ...
    function = 'CONCAT'
    ...

    def as_mysql(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler, connection,
            function='CONCAT_WS',
            template="%(function)s('', %(expressions)s)",
            **extra_context
        )

but I cannot really do that with the agr_suitable_code function.

Is there any way to annotate a queryset with this custom simple function? Any hint would be much appreciated. Thank you in advance.

David Louda
  • 498
  • 5
  • 19
  • 1
    the content of `Func()` needs to be valid SQL, as it will be executed on the SQL server, and your Python function is not it. Try to print the actual SQL query you're running, you'll see it does not make sense. – yedpodtrzitko May 19 '21 at 10:43
  • Okay, is there another way how to achieve such annotation? – David Louda May 19 '21 at 10:49
  • 1
    @DavidLouda Your function is quite complex for a query, try a making a property on your model instead? – Abdul Aziz Barkat May 19 '21 at 11:13
  • can you describe what you are trying to actually do rather than saying "achieve such annotation"? – yedpodtrzitko May 19 '21 at 11:56
  • I am trying to annotate the queryset with values based on other tables which, however, had the Code changed through the function, and for that I believe I need the code of these objects changed similarly so they match – David Louda May 19 '21 at 12:00

0 Answers0