How to create union of two different django-models?

Question

I have two django-models

class ModelA(models.Model):
    title = models.CharField(..., db_column='title')
    text_a = models.CharField(..., db_column='text_a')
    other_column = models.CharField(/*...*/ db_column='other_column_a')


class ModelB(models.Model):
    title = models.CharField(..., db_column='title')
    text_a = models.CharField(..., db_column='text_b')
    other_column = None 

Then I want to merge the two querysets of this models using union

ModelA.objects.all().union(ModelB.objects.all())

But in query I see

(SELECT
`model_a`.`title`,
`model_a`.`text_a`,
`model_a`.`other_column`
FROM `model_a`)

UNION
(SELECT
`model_b`.`title`,
`model_b`.`text_b`
FROM `model_b`)

Of course I got the exception The used SELECT statements have a different number of columns.

How to create the aliases and fake columns to use union-query?

Shrine

Answer 1

You can annotate your last column to make up for column number mismatch.

a = ModelA.objects.values_list('text_a', 'title', 'other_column')
b = ModelB.objects.values_list('text_a', 'title')
        .annotate(other_column=Value("Placeholder", CharField()))

# for a list of tuples
a.union(b)

# or if you want list of dict
# (this has to be the values of the base query, in this case a)

a.union(b).values('text_a', 'title', 'other_column')

Bushman

Answer 2

In SQL query, we can use NULL to define the remaining columns/aliases

(SELECT
`model_a`.`title`,
`model_a`.`text_a`,
`model_a`.`other_column`
 FROM `model_a`)

UNION

(SELECT
`model_b`.`title`,
`model_b`.`text_b`, 
 NULL
 FROM `model_b`)

Goth

Thanks for answer. I know how use union in mysql. I want to know how to describe it in django-models – Shrine

@Shrine I dont have much experience with Django. But based on some searches, you can use Value() or annotate(). check this answer: https://mcmap.net/q/2032754/-how-to-use-union-in-django-add-fake-columns and https://mcmap.net/q/554337/-how-to-add-additional-column-to-django-queryset – Goth

Answer 3

In Django, union operations needs to have same columns, so with values_list you can use those specific columns only like this:

qsa = ModelA.objects.all().values('text_a', 'title')
qsb = ModelB.objects.all().values('text_a', 'title')

qsa.union(qsb)

But there is no way(that I know of) to mimic NULL in union in Django. So there are two ways you can proceed here.

First One, add an extra field in your Model with name other_column. You can put the values empty like this:

other_column = models.CharField(max_length=255, null=True, default=None)

and use the Django queryset union operations as described in here.

Last One, the approach is bit pythonic. Try like this:

a = ModelA.objects.values_list('text_a', 'title', 'other_column')
b = ModelB.objects.values_list('text_a', 'title')

union_list = list()

for i in range(0, len(a)):
     if b[i] not in a[i]:
         union_list.append(b[i])
     union_list.append(a[i])

Hope it helps!!

Andaman

Answer 4

ModelA.objects.all() \
.union(
     ModelB.objects
     .annotate(othercolumn=Value("")
     .all())

Tsan