CODEWITHSUNDEEP
sqldjangodjango-orm
Manuel Santi
Manuel Santi

Reputation: 1132

Convert a complex SQL query in Django ORM

In my project i have an SQL query for extract some results from two tables like thisone:

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

here my models:

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

i wolud to use this query in my django project using ORM but i try to do :

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

but is not correct, i don't know how i can link two tables, select specific fields and filter for that condition in django ORM

Can someone help me please? So many thanks in advance

Manuel

Upvotes: 0

Views: 550

Answers (1)

Dean Elliott
Dean Elliott

Reputation: 1323

Your Django query would be:

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",
)

Or to access the fields without getting the values during the query:

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

Read here about select_related which does a join.

Read here for .values() and how to access related fields.

See here for __range.

Upvotes: 2

Related Questions