Joining different models in Django

Question

Let's say I have this data model:

class Workflow(models.Model):
  ...

class Command(models.Model):
  workflow = models.ForeignKey(Workflow)
  ...

class Job(models.Model):
  command = models.ForeignKey(Command)
  ...

Suppose somewhere I want to loop through all the Workflow objects, and for each workflow I want to loop through its Commands, and for each Command I want to loop through each Job. Is there a way to structure this with a single query?

That is, I'd like Workflow.objects.all() to join in its dependent models, so I get a collection that has dependent objects already cached, so workflows[0].command_set.get() doesn't produce an additional query.

Is this possible?

Answer 1

The other way around it's easy since you can do

all_jobs = Job.objects.select_related().all()

And any job.command or job.command.workflow won't produce additional query.

Not sure if it's possible with a Workflow query.

Answer 2

I think the only way you could do that would be using django.db.connection and write your own query.

Since this would be iterating all instances of Job (your ForeignKeys aren't set null) anyway you could select all Job's and then group them outside of the ORM