Im trying to make a complex queryset and I want to include my ForeignKeys names instead of pk. I'm using ajax to get a live feed from user inputs and print the results on a DataTable but I want to print the names instead of the pk. Im getting a queryset and when I console.log it, sensor_name is not in there. My models are like this:

class TreeSensor(models.Model):

    class Meta:
        verbose_name_plural = "Tree Sensors"

    field = models.ForeignKey(Field, on_delete=models.CASCADE)
    sensor_name = models.CharField(max_length=200, blank=True)
    datetime = models.DateTimeField(blank=True, null=True, default=now)
    longitude = models.DecimalField(max_digits=22, decimal_places=16, blank=True, null=True)
    latitude = models.DecimalField(max_digits=22, decimal_places=16, blank=True, null=True)

class TreeSensorMeasurement(models.Model):

    class Meta:
        verbose_name_plural = "Tree Sensor Measurements"

    sensor = models.ForeignKey(TreeSensor, on_delete=models.CASCADE)
    datetime = models.DateTimeField(blank=True, null=True, default=None)

    soil_moisture_depth_1 = models.DecimalField(max_digits=15, decimal_places=2)
    soil_moisture_depth_2 = models.DecimalField(max_digits=15, decimal_places=2)
    soil_moisture_depth_1_filtered = models.DecimalField(max_digits=15, decimal_places=2, blank=True, null=True)
    soil_moisture_depth_2_filtered = models.DecimalField(max_digits=15, decimal_places=2, blank=True, null=True)
    soil_temperature = models.DecimalField(max_digits=15, decimal_places=2)

And my view looks like this(I've omitted the non-essential code):

field_list = Field.objects.filter(user=request.user)   
tree_sensors = TreeSensor.objects.filter(field_id__in=field_list.values_list('id', flat=True))   
statSensors = (TreeSensorMeasurement.objects
              .filter(sensor_id__in=tree_sensors.values_list('id', flat=True))                                
              .filter(datetime__date__lte=To_T[0]).filter(datetime__date__gte=From_T[0])                      
              .filter(soil_moisture_depth_1__lte=To_T[1]).filter(soil_moisture_depth_1__gte=From_T[1]) 
              .filter(soil_moisture_depth_2__lte=To_T[2]).filter(soil_moisture_depth_2__gte=From_T[2])    
              .filter(soil_temperature__lte=To_T[3]).filter(soil_temperature__gte=From_T[3])                  
              .order_by('sensor', 'datetime'))
TreeData = serializers.serialize('json', statSensors)

The code above works correctly but I cant figure out the twist I need to do to get the TreeSensors name instead of pk in the frontend. An example of how I receive one instance in the frontend:

datetime: "2022-11-20T13:28:45.901Z"
​​​sensor: 2
​​​soil_moisture_depth_1: "166.00"
​​​soil_moisture_depth_1_filtered: "31.00"
​​​soil_moisture_depth_2: "171.00"
​​​soil_moisture_depth_2_filtered: "197.00"
soil_temperature: "11.00"

CodePudding user response：

Because sensor_name is at the other end of a foreign key field in your TreeSensorMeasurement object, you can grab it using select_related, as in

          .filter(soil_temperature__lte=To_T[3]).filter(soil_temperature__gte=From_T[3])   
          .select_related('sensor')               
          .order_by('sensor', 'datetime'))

In a template you could then refer to it, without further DB lookups, as

{% for ss in statsensors }}
    {{ ss.sensor.sensor_name }}
{% endfor }}

If you're using Django Rest Framework you'll need to do a little more - this is adpated from the example at DRF nested relationshipos

class TreeSensorSerializer(serializers.ModelSerializer):
    class Meta:
        model = TreeSensor
        fields = ['sensor_name']

class TreeSensorMeasurementSerializer(serializers.ModelSerializer):
    sensor = TreeSensorSerializer(read_only=True)

    class Meta:
        model = TreeSensorMeasurement
        fields = ['sensor', 
                  'datetime', 
                  'soil_moisture_depth1',
                  'soil_moisture_depth2',
                  'soil_temoperature',
                  ]

CodePudding user response：

Try adding a read-only serializer field in TreeSensorMeasurementSerializer

sensor_name = serializer.ReadOnlyField(source='sensor.sensor_name')