在Django ORM中转换一个复杂的SQL查询

Question

在我的项目中，我有一个SQL查询，用于从以下两个表中提取一些结果：

SELECT
read_date, unit_id, device_id, proj_code, var_val
FROM
public.api_site_varsresults AS SV, public.api_site_results AS SR
WHERE 
(read_date >= '2021-06-21' AND read_date <= '2021-06-24') AND SV.id_res_id = SR.id

这是我的型号：

class Results(models.Model):
id = models.AutoField(primary_key=True)
device = models.ForeignKey(Device, null=True, on_delete=models.SET_NULL)
proj_code = models.CharField(max_length=400)
res_key = models.SlugField(max_length=80, verbose_name="Message unique key", unique=True)
read_date = models.DateTimeField(verbose_name="Datetime of vals readings")
unit = models.ForeignKey(ModbusDevice, null=True, on_delete=models.SET_NULL)
def __str__(self):
return self.device
class Meta:
indexes = [
models.Index(fields=['device', 'unit', 'proj_code']),
]

class VarsResults(models.Model):
id = models.AutoField(primary_key=True)
id_res = models.ForeignKey(Results, related_name="mainres", on_delete=models.CASCADE)
var_id = models.ForeignKey(ModbusVariable, null=True, on_delete=models.SET_NULL)
var_val = models.CharField(max_length=400, blank=True)
var_hash = models.CharField(max_length=400)
def __str__(self):
return self.var_hash
class Meta:
indexes = [
models.Index(fields=['id_res', 'var_id']),
]

我想在我的django项目中使用ORM来使用这个查询，但我尝试这样做：

varsresults.objects.filter(<conditions>).fields(<fields>)

但这是不正确的，我不知道如何链接两个表，选择特定的字段，并在django ORM 中过滤该条件

有人能帮帮我吗？提前感谢

Manuel

Answer 1

您的Django查询将是：

start_date = datetime.date(2021, 06, 21)
end_date = datetime.date(2021, 06, 24)
var_results = VarsResults.objects.filter(
id_res__read_date__range=(start_date, end_date)
).select_related(
"id_res"
).values(
"id_res__read_date",
"id_res__unit_id",
"id_res__device_id",
"id_res__proj_code",
"var_val",
)

或者在查询过程中访问字段而不获取值：

start_date = datetime.date(2021, 06, 21)
end_date = datetime.date(2021, 06, 24)
var_results = VarsResults.objects.filter(
id_res__read_date__range=(start_date, end_date)
).select_related(
"id_res"
)
read_date = var_results.id_res.read_date
unit_id = var_results.id_res.unit_id

请阅读此处有关执行联接的select_related的信息。

阅读此处了解.values()以及如何访问相关字段。

参见此处了解__range。