How to query tuples of columns in Django database queries?

Question

I have some table ports(switch_ip, slot_number, port_number, many, more, columns) and would like to achieve the following PostgreSQL query using Django:

SELECT switch_ip, array_agg((slot_number, port_number, many, more, columns) ORDER BY slot_number, port_number) info
FROM ports
GROUP BY switch_ip
ORDER BY switch_ip

Using django.contrib.postgres.aggregates here's what I got so far:

Port.objects \
    .values('switch_ip') \
    .annotate(
        info=ArrayAgg('slot_number', ordering=('slot_number', 'port_number'))
    ) \
    .order_by('switch_ip')

I am unable to include more than one column in the ArrayAgg. None of ArrayAgg(a, b, c), ArrayAgg((a, b, c)), ArrayAgg([a, b, c]) seem to work. A workaround could involve separate ArrayAggs for each column and each with the same ordering. I would despise this because I have many columns. Is there any nicer workaround, possibly more low-level?

I suspect this is no issue with ArrayAgg itself but rather with tuple expressions in general. Is there any way to have tuples at all in Django queries? For example, what would be the corresponding Django of:

SELECT switch_ip, (slot_number, port_number, many, more, columns) info
FROM ports

If this is not yet possible in Django, how feasible would it be to implement?

Just to understand your problem, why do you want these values to be returned in a tuple, instead of just building the tuple in python? — Victor
– Victor, Commented Mar 28, 2020 at 4:51
@GrandPhuba To solve my original problem: Not having to annotate with multiple info1=ArrayAgg(a, ordering=o), info2=ArrayAgg(b, ordering=o), info3=ArrayAgg(c, ordering=o) where o is the same ordering every time. If ArrayAgg could work on tuples it would simply be something like info=ArrayAgg((a, b, c), ordering=o). Because I suspect this is no problem specific to ArrayAgg I broadened my question to tuples in Django in general. — purefanatic
– purefanatic, Commented Mar 28, 2020 at 11:19

Anton M. · Accepted Answer · 2021-12-07 20:07:46Z

I have spent lot of time searching for a working solution and here is a full recipe with code example.

You need to define Array "function" with square brackets in template

from django.db.models.expressions import Func

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

You need to define output field format (it must be array of some django field). For example an array of strings

from django.contrib.postgres.fields import ArrayField
from django.db.models.fields import CharField

out_format = ArrayField(CharField(max_length=200))

Finally make an ArrayAgg expression

from django.db.models import F

annotate = {'2-fields': ArrayAgg(Array(F('field1'), F('field2'), output_field=out_format), distinct=True) }
model.objects.all().annotate(**annotate)

(Optional) If field1 or field2 are not CharFields, you may include Cast as an argument of Array

from django.db.models.functions import Cast


annotate = {'2-fields': ArrayAgg(Array(Cast(F('field1'), output_field=CharField(max_length=200)), F('field2'), output_field=out_format), distinct=True) }

purefanatic · Accepted Answer · 2020-03-31 08:56:54Z

0

Having done a bit more research I guess one could add the missing tuple functionality as follows:

Create a new model field type named TupleField. The implementation might look kind of similar to django.contrib.postgres.fields.ArrayField. TupleField would be rather awkward because I don't think any RDBMS allows for composite types to be used as column types so usage of TupleField would be limited to (possibly intermediate?) query results.
Create a new subclass of django.db.models.Expression which wraps multiple expressions on its own (like Func in general, so looking at Func's implementation might be worthwile) and evaluates to a TupleField. Name this subclass TupleExpression for example.

Then I could simply annotate with ArrayAgg(TupleExpression('slot_number', 'port_number', 'many', 'more', 'columns'), ordering=('slot_number', 'port_number')) to solve my original problem. This would annotate each switch_ip with correctly-ordered arrays of tuples where each tuple represents one switch port.

answered Mar 31, 2020 at 8:56

purefanatic

1,0332 gold badges11 silver badges25 bronze badges

1 Comment

purefanatic Over a year ago

Downvoting this because adopting any NoSQL database is likely to be more productive and fulfilling than wrestling with Django internals to achieve something as basic as a tuple. Oh wait I can't. Why is the downward arrow shown then?

Collectives™ on Stack Overflow

How to query tuples of columns in Django database queries?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related