CODEWITHSUNDEEP
pythondjangopostgresqldjango-orm
Pax0r
Pax0r

Reputation: 2473

Specify order of columns in SELECT with UNION using Django ORM

How could I specify the order of columns in SELECT query in Django ORM?

I am trying to union elements from two tables, but apparently elements in union are matched by the order of columns in SELECT, instead of the names of the columns (even if name of the columns are the same).

Consider following Models:

class Person(models.Model):
    first_name = models.CharField(max_length=256)
    last_name = models.CharField(max_length=256)
    age = models.IntegerField()


class Car(models.Model):
    number = models.IntegerField()
    brand = models.CharField(max_length=256)
    name = models.CharField(max_length=256)

and following piece of code:

Person.objects.create(first_name="John", last_name="Smith", age=25)
Car.objects.create(number=42, name="Cybertruck", brand="Tesla")

q1 = Person.objects.all().annotate(name=F('first_name'), group=F('last_name'), number=F('age')).values(
            'name', 'group', 'number')
q2 = Car.objects.all().annotate(group=F('brand')).values('name', 'group', 'number')

data = q1.union(q2)
print(data.query)
assert list(data) == [
    {'name': 'John', 'group': 'Smith', 'number': 25},
    {'name': 'Cybertruck', 'group': 'Tesla', 'number': 42},
])

As you can see I put correct order in .values().

What one could expect is that columns in union would be matched in the order passed to values (or by column names), but this is what happens:

SELECT "testunion_person"."first_name" AS "name", "testunion_person"."last_name" AS "group", "testunion_person"."age" AS "number" FROM "testunion_person" UNION SELECT "testunion_car"."name", "testunion_car"."number", "testunion_car"."brand" AS "group" FROM "testunion_car"

In the queries "testunion_car"."number" is before "testunion_car"."brand", which makes the Car in UNION have a values: {'name': 'Cybertruck', 'group': '42', 'number': 'Tesla'}

EDIT: I am using 2.2 (LTS) version of Django

Upvotes: 12

Views: 3587

Answers (3)

Thierry Lam
Thierry Lam

Reputation: 46284

Instead of specifying the alias under annotate(), you can also specify them straight under values():

q1 = Person.objects.all().values(
    name=F('first_name'), group=F('last_name'), xnumber=F('age'))
q2 = Car.objects.all().values(
    'name', group=F('brand'), xnumber=F('number'))

I noticed that even then, it wasn't ordering the fields properly. I renamed the number field to xnumber to avoid conflicts with the model field of the same name and everything is grouped properly.

Upvotes: 6

Waket Zheng
Waket Zheng

Reputation: 6351

Not a Django bug. Although query columns not sorted as values, the queryset display the right order:

In [13]: print(data)
<QuerySet [{'name': 'Cybertruck', 'group': 42, 'number': 'Tesla'}, {'name': 'John', 'group': 'Smith', 'number': 25}]>

It is because the data will be sorted after fetch from database. Source code snippet of QuerySet:

class QuerySet:
    def __iter__(self):
        """
        The queryset iterator protocol uses three nested iterators in the
        default case:
            1. sql.compiler.execute_sql()
               - Returns 100 rows at time (constants.GET_ITERATOR_CHUNK_SIZE)
                 using cursor.fetchmany(). This part is responsible for
                 doing some column masking, and returning the rows in chunks.
            2. sql.compiler.results_iter()
               - Returns one row at time. At this point the rows are still just
                 tuples. In some cases the return values are converted to
                 Python values at this location.
            3. self.iterator()
               - Responsible for turning the rows into model objects.
        """
        self._fetch_all()
        return iter(self._result_cache)

Upvotes: 2

Yann
Yann

Reputation: 2532

You can set the order of the fields using .values_list.

qs1 = Person.objects.values_list('name', 'group', 'number')
qs2 = Car.objects.values_list('brand', 'name', 'number')
qs1.union(qs2)

Check the docs for more detailed explanation.

Upvotes: 1

Related Questions