0

If I have 2 models:

class Hero(models.Model):
    hero_name = models.CharField(max_length=50)
    hero_age = models.PositiveSmallIntegerField()
    hero_identity = models.TextField(max_length=50)

    def __str__(self):
        return self.hero_name

class Villain(models.Model):
    villain_name = models.CharField(max_length=50)
    villain_age = models.PositiveSmallIntegerField()
    villain_identity = models.TextField(max_length=50)

    def __str__(self):
        return self.villain_name

and I create some test instances:

Hero(hero_name="Superman", hero_age=30, hero_identity="Clark Kent").save()
Hero(hero_name="Iron Man", hero_age=35, hero_identity="Tony Stark").save()
Hero(hero_name="Spider-Man", hero_age=18, hero_identity="Peter Parker").save()

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

Listing them individually works fine, but listing them using a union breaks the order somehow:

>>> from django.db.models import F
>>> from myapp.models import Hero, Villain

>>> for hero in Hero.objects.all().annotate(name=F("hero_name"), age=F("hero_age"), identity=F("hero_identity")).values("name", "age", "identity"):
...     print(hero)

{'name': 'Superman', 'age': 30, 'identity': 'Clark Kent'}
{'name': 'Iron Man', 'age': 35, 'identity': 'Tony Stark'}
{'name': 'Spider-Man', 'age': 18, 'identity': 'Peter Parker'}

>>> for villain in Villain.objects.all().annotate(name=F("villain_name"), age=F("villain_age"), identity=F("villain_identity")).values("name", "age", "identity"):
...     print(villain)

{'name': 'Green Goblin', 'age': 45, 'identity': 'Norman Osborn'}
{'name': 'Red Skull', 'age': 38, 'identity': 'Johann Schmidt'}
{'name': 'Vulture', 'age': 47, 'identity': 'Adrian Toomes'}

>>> all = Hero.objects.all().annotate(name=F("hero_name"), age=F("hero_age"), identity=F("hero_identity")).union(Villain.objects.all().annotate(name=F("villain_name"), age=F("villain_age"), identity=F("villain_identity")))
>>> for person in all.values("name", "age", "identity"):
...     print(person)

{'name': 1, 'age': 'Green Goblin', 'identity': 45}
{'name': 1, 'age': 'Superman', 'identity': 30}
{'name': 2, 'age': 'Iron Man', 'identity': 35}
{'name': 2, 'age': 'Red Skull', 'identity': 38}
{'name': 3, 'age': 'Spider-Man', 'identity': 18}
{'name': 3, 'age': 'Vulture', 'identity': 47}

The values in the name column appear to be the values of the id column for each row, the name values are in the age column, the age values in the identity column, and the identity values don't appear at all.

Note: To list them individually, I obviously don't need that annotate - I can just use the original column names - but with the annotate, I'm keeping the data as close to the pre-union operation as possible.

Note: This is using Django version 3.2.7

John
  • 2,551
  • 3
  • 30
  • 55
  • Why not to annotate id column as well, so you will have 4 annotated column instead of one and give it one try? – Husam Alhwadi Dec 23 '21 at 19:30
  • @HusamAlhwadi annotate just adds columns. It doesn't remove the columns that were going to be returned in the first place. That means the `id` column is already present in the output, and unless you add another `id` column with a new name, Django won't let you add a duplicate `id` column. I'm not sure how adding a new `id` column with a new name will help, if that's what you meant. – John Dec 24 '21 at 13:41
  • @DušanMaďar I read through those answers, but honestly couldn't follow it on the first pass. I'll be looking at it again later today. – John Dec 24 '21 at 13:42
  • you already have annotated 3 columns except id column however your issue is in the values of these columns , so I suggest you to annotate id column so you will have 4 columns instead of 3, hopes this will eliminate the mismatching in column values, bearing in mind that only value of id column is available under name column, really I didn't face such case before but I just suggest ... – Husam Alhwadi Dec 24 '21 at 15:51
  • @HusamAlhwadi annotate doesn't do what you think it does. You can't annotate the `id` column because it's already there. It's not there because I listed it explicitly - I didn't. It's there implicitly. If you try to annotate it, you get a `ValueError: The annotation 'id' conflicts with a field on the model.` exception. I appreciate you trying to help, but what you suggest can't be done. – John Dec 24 '21 at 16:48
  • My actual models and queries are quite large and complex, and I'm having a hard time boiling them down to a simple example. Doing some reading and experimenting, I can solve this sample by using `values` instead of `annotate`, but it doesn't help with my real problem. I'll try to re-ask the question with a slightly different example. – John Dec 24 '21 at 17:49

0 Answers0