Django QuerySets 复杂查询

在 django 中使用 querysets API 进行复杂查询，要求同时获得以下信息：

• 获取某个用户的设备
• 获取每个设备所属的传感器
• 获取每个传感器最近记录的值和时间戳

使用 plain SQL 可以轻松实现此查询，但在 django 中却遇到了困难。尝试了 django shell 和查阅文档，但仍无法找到解决方案。

最终使用的模型如下：

class User(AbstractBaseUser):
    email = models.EmailField()

class Device(models.Model):
    user = models.ForeignKey(User)
    name = models.CharField()

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

class SensorType(models.Model):
    name = models.CharField()
    unit = models.ForeignKey(Unit)

class Sensor(models.Model):
    gpio_port = models.IntegerField()
    device = models.ForeignKey(Device)
    sensor_type = models.ForeignKey(SensorType)

class SensorData(models.Model):
    sensor = models.ForeignKey(Sensor)
    value = models.FloatField()
    timestamp = models.DateTimeField()

SQL 查询如下：

SELECT acc.email, 
       dev.name as device_name, 
       stype.name as sensor_type,
       sen.gpio_port as sensor_port,
       sdata.value as sensor_latest_value, 
       unit.name as sensor_units, 
       sdata.latest as value_received_on
FROM devices_device as dev
INNER JOIN accounts_user  as acc on dev.user_id = acc.id
INNER JOIN devices_sensor  as sen on sen.device_id = dev.id
INNER JOIN devices_sensortype as stype on stype.id = sen.sensor_type_id
INNER JOIN devices_unit as unit on unit.id = stype.unit_id
LEFT JOIN (
            SELECT MAX(sd.timestamp) latest, sd.value, sensor_id
            FROM devices_sensordata as sd
            INNER JOIN devices_sensor as s ON s.id = sd.sensor_id
        GROUP BY sd.sensor_id) as sdata on sdata.sensor_id= sen.id
WHERE acc.id = 1
ORDER BY dev.id

2. 解决方案

在 django 中，使用 Subquery 和 Exists 来实现此查询：

latest_sensor_data = Sensor.objects.annotate(is_latest=~Exists(SensorData.objects.filter(sensor=OuterRef('id'),timestamp__gt=OuterRef('sensordata__timestamp')))).filter(is_latest=True)
user_devices = latest_sensor_data.filter(device__user=1)

生成的 SQL 如下：

    SELECT
  "devices_sensor"."id",
  "devices_sensor"."gpio_port",
  "devices_sensor"."sensor_type_id",
  "devices_sensor"."device_id",
  (
    NOT EXISTS(
      SELECT
        U0."id",
        U0."sensor_id",
        U0."value",
        U0."timestamp"
      FROM
        "devices_sensordata" U0
      WHERE
        (
          U0."sensor_id" = ("devices_sensor"."id")
          AND U0."timestamp" > (
            SELECT
              "devices_sensordata"."timestamp"
            FROM
              "devices_sensordata"
            WHERE
              "devices_sensordata"."sensor_id" = ("devices_sensor"."id")
            ORDER BY
              "devices_sensordata"."timestamp" DESC
            LIMIT 1
          )
        )
    ) = True
  ) AS "is_latest"
FROM
  "devices_sensor"
ORDER BY
  "devices_sensor"."id" ASC

最终可以通过 user_devices.values_list('device__name','sensor_type__name', 'gpio_port','sensordata__value', 'sensor_type__unit__name', 'sensordata__timestamp').order_by('device__name') 获取查询结果。