Django queryset StringAgg on arrayfield-CodePudding

I have some data which includes sizes, much like the model below.

class Product(models.Model):
    width = models.CharField()
    height = models.CharField()
    length = models.CharField()

Through annotation we have a field called at_size which produces data like:

[None, None, None]
['200', '000', '210']
['180', None, None]

This was accomplished like so (thanks to: )https://stackoverflow.com/a/70266320/5731101:

    class Array(Func):
        template = '%(function)s[%(expressions)s]'
        function = 'ARRAY'

    out_format = ArrayField(CharField(max_length=200))

    annotated_qs = Product.objects.all().annotate(
        at_size=Array(F('width'), F('height'), F('length'), 
            output_field=out_format)
    )

I'm trying to get this to convert into:

''
'200 x 000 x 210'
'180'

In code, this could a bit like ' x '.join([i for i in data if i]). But as I need to accomplish this with database functions it's a bit more challenging.

I've been playing with StringAgg, but I keep getting:

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

It looks like I need to make sure the None values are excluded from the initial Array-func to begin with. But I'm not sure where to get started here. How can I accomplish this?

CodePudding user response：

Product.objects.annotate(display_format=Concat(F('width'), '×', F('height'), '×', F('length')))

Should do the trick no?

No need to over complicate this, let's keep it nice and simple and use the database to concatenate the 3 strings and separate them with the multiplication symbol (obviously replace if you prefer another character).

Take a look at the docs over here: https://docs.djangoproject.com/en/4.1/ref/models/database-functions/#concat

CodePudding user response：

Turns out the problem was two-fold.

Cleaning out the Null values could be done through using array_remove
glueing the strings with a delimiter through StringAgg only works if the input are strings. But since we use an array, that wasn't the way to go. Instead use array_to_string

The final result looks like:

    class Array(Func):
        # https://www.postgresql.org/docs/9.6/functions-array.html
        template = '%(function)s[%(expressions)s]'
        function = 'ARRAY'

    class ArrayRemove(Func):
        # https://www.postgresql.org/docs/9.6/functions-array.html
        function = 'array_remove'

    class ArrayToString(Func):
        # https://stackoverflow.com/a/57873772/5731101
        function = 'array_to_string'

    out_format = ArrayField(CharField(max_length=200))

    annotated_qs = annotated_qs.annotate(
        at_size=ArrayToString(
            ArrayRemove(
                Array(F('width'), F('height'), F('length'), output_field=out_format),
                None,                
            ),
            Value(" x "),
            Value(''),
            output_field=CharField(max_length=200),
        )
    )

this produces the desired format:

for product in annotated_qs:
    print(product.at_size)

180 x 000 x 200
180 x 026 x 200
180 x 7 x 200
180 x 000 x 200
200 x 000 x 220
180 x 000 x 200
175 x 230 x 033
160 x 000 x 200

60 x 220