将SQL查询转换为Django查询

Question

我正在尝试把SQL查询转换成Django查询，但一直没成功，有人能帮帮我吗？

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)

或者这样

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)

在这里更新了问题

我正在使用Django的Django REST框架，序列化net_rest模型，基本上我在做一个与地理信息系统（GIS）相关的项目，需要制作一个REST API来展示数据。

这是我的一些模型

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

这是视图

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

sql django 数据序列化 rest api GIS 模型视图

将SQL查询转换为Django查询

1 个回答

撰写回答