[Solved]-Specify order of columns in SELECT with UNION using Django ORM

6👍

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.

2👍

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)

1👍

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.

👤Yann

Leave a comment