How to create Union in Django queryset

[Shoaib Ijaz]

Sorry for duplicate post How to create Union

I am using Django REST Framework in project and I want to create union two different Models.

My Models

class A(models.Model):
    name = models.CharField(max_length=240, blank=True)
    geometry = models.GeometryField(blank=True, null=True)
    abwrapper= models.ForeignKey(ABWrapper)

    class Meta:
        db_table = 'tbl_a'

class B(models.Model):
    name = models.CharField(max_length=240, blank=True)
    link = models.IntegerField(blank=True, null=True)
    geometry = models.GeometryField(blank=True, null=True)
    abwrapper= models.ForeignKey(ABWrapper)

    class Meta:
        db_table = 'tbl_b'

I am trying to create this query

SELECT id,name FROM tbl_a UNION (SELECT b.id,b.name From tbl_b b)

My attempt for union

a = A.objects.values_list('id')
b = B.objects.values_list('id')
queryset = a | b

Error:
AssertionError: Cannot combine queries on two different base models.

Now i tried with parent Model in this way

class ABWrapper(models.Model):
    objects = models.GeoManager()
    class Meta:
        db_table = u'ab_wrapper'

Added this model as ForeignKey above both Models

a = ABWrapper.objects.filter(a__isnull=False).values('a__id')
b = ABWrapper.objects.filter(b__isnull=False).values('b__id')
queryset = a | b

Error:
TypeError: Merging 'GeoValuesQuerySet' classes must involve the same values in each case.

Another attempt by making alias

a = ABWrapper.objects.filter(a__isnull=False).extra(select={'tempID':'a__id'}).values_list('tempID')
b = ABWrapper.objects.filter(b__isnull=False).extra(select={'tempID':'b__id'}).values_list('tempID')
queryset = a | b

Error:
ValueError: When merging querysets using 'or', you cannot have extra(select=...) on both sides.

I have searched on it, mostly answered this issue as using list for both models. But I don't want to use list as I am using Django Rest Framework so I need QuerySet. So my question if I use list for union can I convert resulting list into QuerySet.

Note: I don't want to use SQL Query in Django

Is there any other way to do this task?

[Russell Keith-Magee]

Why not? That would seem to be the exact answer you need.

You appear to have a very specific idea of the SQL query you want to
issue, including UNION clauses (an operator that Django's ORM doesn't
support) and "extra" columns (which means the data you want is outside
your regular Django model). Getting this query in 100% native Django
query sets is going to be somewhere between difficult and impossible.

However, you don't have to drop right back to SQL cursors - Django has
raw query sets for exactly this purpose:

ABWrapper.objects.raw("SELECT …. FROM … WHERE")

This will return ABWrapper objects contained in a query set object
that should be compatible with Django REST Framework, but you get
those objects by providing the exact SQL you want to execute.

See:

https://docs.djangoproject.com/en/dev/topics/db/sql/

for more details

Yours,
Russ Magee %-)

[Shoaib Ijaz]

I don't want use SQL query in django

[Thomas Lockhart]

On 4/16/14 11:51 PM, Shoaib Ijaz wrote:

I don't want use SQL query in django

OK. You *could* have class B inherit from class A:

class B(A):
  link = models.IntegerField(blank=True, null=True)

and then do your query on A:

ulist = A.objects.all()

hth

                            - Tom

[Simon Charette]

I'm not sure this will work but you could try creating a VIEW on the database side that does the union there and access it through an un-managed model.

CREATE VIEW view_name AS SELECT id, name FROM a UNION SELECT id, name FROM b;

class C(models.Model):
    name = models.CharField()

    class Meta:
        db_table = 'view_name'
        managed = False