CODEWITHSUNDEEP
pythondjango
John
John

Reputation: 2952

How can I control the column order using 'annotate', 'values' and 'union' in Django?

I have 2 models:

class Hero(models.Model):
    name = models.CharField(max_length=50)
    age = models.PositiveSmallIntegerField()
    identity = models.TextField(max_length=50)

class Villain(models.Model):
    villain_name = models.CharField(max_length=50)
    age = models.PositiveSmallIntegerField()
    identity = models.TextField(max_length=50)

and I create some test instances:

Hero(name="Superman", age=30, identity="Clark Kent").save()
Hero(name="Iron Man", age=35, identity="Tony Stark").save()
Hero(name="Spider-Man", age=18, identity="Peter Parker").save()

Villain(villain_name="Green Goblin", age=45, identity="Norman Osborn").save()
Villain(villain_name="Red Skull", age=38, identity="Johann Schmidt").save()
Villain(villain_name="Vulture", age=47, identity="Adrian Toomes").save()

Since the Villain model doesn't have a name field, we use annotation before doing a union.

Then, using them in a union produces results where the columns aren't all where they should be:

>>> from django.db.models import F
>>> characters = Hero.objects.all().values('name', 'age', 'identity').union(Villain.objects.all().annotate(name=F("villain_name")).values('name', 'age', 'identity'))
>>> for character in characters:
...     print(character)
{'name': 38, 'age': 'Johann Schmidt', 'identity': 'Red Skull'}
{'name': 45, 'age': 'Norman Osborn', 'identity': 'Green Goblin'}
{'name': 47, 'age': 'Adrian Toomes', 'identity': 'Vulture'}
{'name': 'Iron Man', 'age': 35, 'identity': 'Tony Stark'}
{'name': 'Spider-Man', 'age': 18, 'identity': 'Peter Parker'}
{'name': 'Superman', 'age': 30, 'identity': 'Clark Kent'}

Looking at the raw sql queries, we see this:

>>> str(Hero.objects.all().values('name', 'age', 'identity').query)
'SELECT "myapp_hero"."name", "myapp_hero"."age", "myapp_hero"."identity" FROM "myapp_hero"'

>>> str(Villain.objects.all().annotate(name=F("villain_name")).values('name', 'age', 'identity').query)
'SELECT "myapp_villain"."age", "myapp_villain"."identity", "myapp_villain"."villain_name" AS "name" FROM "myapp_villain"'

The auto-generated sql contains columns that aren't in the same order for the two queries.

How can I make these in the same order, so that the resulting queryset of my union isn't messed up?

PS. Yes, this is very similar to the question asked here, but after a long explanation of why it happens, the end result of the answer to that question was:

please make sure that the ordering of fields in generated SQL is always correct

which helps, but doesn't answer the question of how to fix it.

Upvotes: 1

Views: 624

Answers (1)

Iain Shelvington
Iain Shelvington

Reputation: 32274

From the docs on union

Passing different models works as long as the SELECT list is the same in all QuerySets (at least the types, the names don’t matter as long as the types are in the same order)

When you add the annotation to values() it always comes after the non-annotated columns, however since the names don't matter and you only need the columns to be in the same order you can discard this annotation

Hero.objects.values(
    'name', 'age', 'identity'
).union(Villain.objects.values(
    'villain_name', 'age', 'identity'
))

Upvotes: 1

Related Questions