I am working with Django and I need to create an API to retrieve the measures according to the type of sensors, depending of three tables (image of the Schema of my tables)
- Measures contains all measures according to an ID of the sensors
- Sensors contains the sensor information and the type of the sensor
- sensor_types contains the information of the type (type name, unit, etc)
I need to know (at least) the sensor name and ID, the type of the sensor and the measures for the sensors, and the unit of the measures.
I first tried to retrieve the sensor information and the type the sensor belong to:
class TypeViewSet(viewsets.ReadOnlyModelViewSet):
"""
API: return the measures of several sensors according to a type of sensors and a date range.
A type is generaly sensors with the same unit (°C, %, kPa)
"""
# See serializers.py
serializer_class = TypesSerializer
def get_queryset(self):
# get the params
idt = self.kwargs['idtype']
# get the date "from"
start = self.request.query_params.get('start')
# get the date "to"
end = self.request.query_params.get('end')
type = SensorTypes.objects.filter(id_sensor_type=idt)
sensors = Sensors.objects.filter(sensor_types_id_sensor_type=idt)\
.select_related('sensor_types_id_sensor_type')
print(sensors.query)
return sensors
My TypesSerializer file look like:
class SensorTypeSerializer(ser.ModelSerializer):
class Meta:
fields = ("id_sensor_type","sensor_type_name","sensor_type_longname","measure_unit")
model = SensorTypes
class TypesSerializer(ser.ModelSerializer):
sensor_types_id_sensor_type=SensorTypeSerializer(
read_only=True
)
#"sensor_type_name", "measure_unit",
class Meta:
fields = ("id_sensor","sensor_name","sensor_types_id_sensor_type")
model = Sensors
and I have this result (you only see two sensors but there are more bellow. (image of the result)
If now, you look at:
{
"id_sensor": 1,
"sensor_name": "b1",
"sensor_types_id_sensor_type": {
"id_sensor_type": 2,
"sensor_type_name": "te",
"sensor_type_longname": "Températures",
"measure_unit": "°C"
}
},
I need to have all measures for that sensor. (Later, I will add a date range)
My problem I guess you understand my need, but I do not know if I started well and I do not know how to add a second join to get the measures from the Measures table.
I am a bit confuse, because previously I could retrieve the measures according to a station ID as the following:
class SensorViewSet(viewsets.ReadOnlyModelViewSet):
"""
API: return the measures of a sensors according to the ID of the sensor and a date range
"""
# See serializers.py
serializer_class = MeasuresSerializer
def get_queryset(self):
#get the params
ids = self.kwargs['idsensor']
# get the date "from"
start = self.request.query_params.get('start')
# get the date "to"
end = self.request.query_params.get('end')
# if param end (from) is empty or does not exist
if end is None or len(end) <= 0:
# Get the last saved measure date/time of a sensor
latest_measure = Measures.objects.filter(sensors_id_sensor=ids).order_by('-measure_created').first()
#save the date
end = latest_measure.measure_created
else:
# get the date from the param end, create an object and save the value to end
end = datetime.strptime(end, '%Y-%m-%d %H:%M:%S')
# if the param start (from) is empty or does not exist
if start is None or len(start) <= 0:
# get the date of the latest measure minus 3 days and save it to start
start = end - timedelta(days=3)
else:
# get the value from the param start, create an object and save it to start
start = datetime.strptime(start, '%Y-%m-%d %H:%M:%S')
# Get all measures from the date range
sensor_measures = Measures.objects \
.filter(sensors_id_sensor=ids,
measure_created__range=[start,end]) \
.order_by('measure_created') \
.select_related('sensors_id_sensor')
#print(sensor_measures)
return sensor_measures
and as you can see, I first query the measures table with a select_related to the Sensors table.
But now, I need to filter the sensors according to the id of the type, and to get information about the type as measure_unit.
My question is how to "include" the Sensor_types table to my queryset?
