Convert SQL QUERY to django QUERY

Question

I am trying to convert sql query to django query but failed to do this, can anyone help me

select id,name,round(value::numeric,2) as value, st_transform(geometry, 3857) as geometry
        from net_rest
        where state_id in (1,2) and name = 'height'
        union
        (select d.id,d.restriction,d.value, st_transform(d.geometry, 3857) as geometry from display_rest d
        where d.restriction='height' and condition_id not in (select condition_id 
        from net_rest_conditions where version_id = 2)

OR This

select id,name,value as value, geometry
        from net_rest
        where state_id in (1,2) and name = 'height'
        union
        (select d.id,d.restriction,d.value,geometry from display_rest d
        where d.restriction='height' and condition_id not in (select condition_id 
        from net_rest_conditions where version_id = 2)

Updated the question fro here

I am using django django rest framework serialize net_rest Model, basically i am working on project related to GIS where i have to make rest api to expose data

Here is some of my models

class net_rest(models.Model):
    name = models.CharField(max_length=50, blank=True)
    value = models.FloatField()
    state_id = models.IntegerField(null=True, blank=True)
    geometry = models.GeometryField(null=True, blank=True)
    objects = models.GeoManager()

    class Meta:
        db_table = u'tbl_net_rest'

    def __unicode__(self):
        return '%s' % self.name



class display_rest(models.Model):
    restriction = models.CharField(max_length=45, blank=True)
    link_id = models.IntegerField(blank=True, null=True)
    condition_id = models.IntegerField(blank=True, null=True)
    value = models.FloatField(blank=True, null=True)
    geometry = models.GeometryField(blank=True, null=True)
    class Meta:
        db_table = u'tbl_display_rest'



class net_rest_conditions(models.Model):
    condition_id = models.IntegerField()
    version_id =  models.IntegerField(blank=True, null=True)
    class Meta:
        db_table = u'tbl_net_rest_conditions'

class networkSerializer(serializers.GeoModelSerializer):
    class Meta:
        model = net_rest
        fields = ('id', 'name', 'value', 'geometry')

Here is view

class networkSerializerViewSet(viewsets.ModelViewSet):

    q1 = display_rest.objects.values_list('id', 'name', 'value', 'geometry').filter(restriction='height')\
        .exclude(condition_id__in=net_rest_conditions.objects.filter(version_id=2).values_list('condition_id',flat=True))

    q2 = net_rest.objects.all().filter(Q(name="height"), Q(state_id=1) | Q(state_id=2)).values_list('id', 'value', 'geometry')

    queryset = q1 | q2

    serializer_class = networkSerializer

Nishant Nawarkhede · Accepted Answer · 2014-03-18 11:16:18Z

2

More complex queries are not possible in django , You can use raw queries instead.

<OBJECT>.objects.raw('sql statement goes here')

answered Mar 18, 2014 at 11:16

Nishant Nawarkhede

8,40813 gold badges61 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shoaib Ijaz Over a year ago

is there any other way mean by splitting queries, i have mentioned in my question

Nishant Nawarkhede Over a year ago

@AskQuestion What do u mean by splitting queries ?

Shoaib Ijaz Over a year ago

I have done above as (q1) and (q2) both queries output is correct now i want to make union of these queries

Nishant Nawarkhede Over a year ago

@AskQuestion for union we have to use queryset = q1 | q2 , It looks like u did this in your view.

Shoaib Ijaz Over a year ago

yes but not working for me due to rest framework, it throws exception ' q1 and q2 are not same model '

Collectives™ on Stack Overflow

Convert SQL QUERY to django QUERY

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related