CODEWITHSUNDEEP
mysqldjangodjango-modelsunion
Dmitry
Dmitry

Reputation: 570

How to create union of two different django-models?

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?

Upvotes: 5

Views: 5110

Answers (4)

Jonatan Cloutier
Jonatan Cloutier

Reputation: 929

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

Upvotes: 0

Nick Collins
Nick Collins

Reputation: 126

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')

Upvotes: 8

ruddra
ruddra

Reputation: 51988

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!!

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28844

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`)

Upvotes: 0

Related Questions