Django query on related model

Question

I have models like below

class Scheduler(models.Model):  
    id = <this is primary key>
    last_run = <referencing to id in RunLogs below>  

class RunLogs(models.Model):  
    id = <primary key>  
    scheduler = <referencing to id in Scheduler above>
    overall_status = <String>

Only when the scheduler reaches the scheduled time of the job, RunLogs entry is created.

Now I am querying on RunLogs to show running schedules as below.

current = RunLog.objects\
                     .filter(Q(overall_status__in = ("RUNNING", "ON-HOLD", "QUEUED") |
                             Q(scheduler__last_run__isnull = True))  

The above query gives me all records with matching status from RunLogs but does not give me records from Scheduler with last_run is null.

I understand why the query is behaving so but is there a way to get records from scheduler also with last_run is null ?

Answer 1

I just did the same steps which you followed and found the reason why you where getting all the records after running your query. Here is the exact steps and a solution for this.

Steps

1. Created models

   from django.db import models
   
   class ResourceLog(models.Model):
       id = models.BigIntegerField(primary_key=True)
       resource_mgmt = models.ForeignKey('ResourceMgmt', on_delete=models.DO_NOTHING,
                                         related_name='cpe_log_resource_mgmt')
       overall_status = models.CharField(max_length=8, blank=True, null=True)
   
   
   class ResourceMgmt(models.Model):
       id = models.BigIntegerField(primary_key=True)
       last_run = models.ForeignKey(ResourceLog, on_delete=models.DO_NOTHING, blank=True, null=True)
   

2. Added the data as following:

   resource_log

   +----+----------------+------------------+
   | id | overall_status | resource_mgmt_id |
   +----+----------------+------------------+
   |  1 | RUNNING        |                1 |
   |  2 | QUEUED         |                1 |
   |  3 | QUEUED         |                1 |
   +----+----------------+------------------+
   

   resource_mgmt

   +----+-------------+
   | id | last_run_id |
   +----+-------------+
   |  1 |        NULL |
   |  2 |        NULL |
   |  3 |        NULL |
   |  4 |           3 |
   +----+-------------+
   

   • According to the above table resource_mgmt(4) is referring to resource_log(3). But thing to be noted is, resource_log(3) is not referring to resource_mgmt(4).

3. Ran the following command in python shell

   In [1]: resource_log1 = ResourceLog.objects.get(id=1)
   
   In [2]: resource_log.resource_mgmt
   Out[2]: <ResourceMgmt: ResourceMgmt object (1)>
   In [3]: resource_log1 = ResourceLog.objects.get(id=2)
   
   In [4]: resource_log.resource_mgmt
   Out[4]: <ResourceMgmt: ResourceMgmt object (1)
   In [5]: resource_log1 = ResourceLog.objects.get(id=3)
   
   In [6]: resource_log.resource_mgmt
   Out[6]: <ResourceMgmt: ResourceMgmt object (1)>
   

   from this we can understand that all the resource_log objects are referring to 1st object of resource_mgmt(ie, id=1).

   Q) Why all the objects are referring to 1st object in the resource_mgmt?

   • resource_mgmt is a foreign key field which is not null. Its default value is 1. when you create a resource_log object, if you are not specifying resource_mgmt, it will add the default value there which is 1.

4. Run your query

   In [60]: ResourceLog.objects.filter(resource_mgmt__last_run__isnull = True)
   Out[60]: <QuerySet [<ResourceLog: ResourceLog object (1)>, <ResourceLog: ResourceLog object (2)>, <ResourceLog: ResourceLog object (3)>]>
   

   • This query is returning all three ResourceLog objects because all three are referring to 1st resource_mgmt object which has its is_null value as True

Solution

You actually want to check the reverse relationship.

We can achieve this using two queries:

rm_ids = ResourceMgmt.objects.exclude(last_run=None).values_list('last_run', flat=True)
current = ResourceLog.objects.filter(overall_status__in = ("RUNNING", "QUEUED")).exclude(id__in=rm)

The output is:

<QuerySet [<ResourceLog: ResourceLog object (1)>, <ResourceLog: ResourceLog object (2)>]>

Hope that helps!