django orm - how to join multiple tables

Question

I have a bunch of tables in postgresql (not all the fields are listed)

create TABLE run (
    id integer NOT NULL,
    build_id integer NOT NULL,
);

CREATE TABLE test_info (
    suite_id integer NOT NULL,
    run_id integer NOT NULL,
    test_id integer NOT NULL,
    id integer NOT NULL,
    error_text text
);

CREATE TABLE tool_info (
    id integer NOT NULL,
    tool_id integer,
    revision_id integer,
    test_info_id integer,
);

CREATE TABLE suite_info (
    id integer,
    suite_id integer NOT NULL,
    run_id integer NOT NULL,

);
CREATE TABLE test (
    id integer NOT NULL,
    path text NOT NULL
);

here are the models:

class Run(models.Model):
    build = models.ForeignKey(Build, models.DO_NOTHING)
    date = models.DateTimeField(blank=True, null=True)
    type = models.ForeignKey('Type', models.DO_NOTHING, blank=True, null=True)
    date_finished = models.DateTimeField(blank=True, null=True)
    ko_type = models.ForeignKey('Type', models.DO_NOTHING, db_column='ko_type', blank=True, null=True, related_name='ko_type')
    branch = models.ForeignKey(Branch, models.DO_NOTHING, blank=True, null=True)
    arch = models.ForeignKey('Type', models.DO_NOTHING, db_column='arch_id', blank=True, null=True, related_name='arch_id')

    class Meta:
        managed = False
        db_table = 'run'
        unique_together = (('build', 'date', 'type', 'arch'),)

class TestInfo(models.Model):
    suite = models.ForeignKey(Suite, models.DO_NOTHING)
    run = models.ForeignKey(Run, models.DO_NOTHING)
    test = models.ForeignKey(Test, models.DO_NOTHING)
    status = models.TextField()
    total_execution_time = models.DurationField(blank=True, null=True)
    turnaround_time = models.DurationField(blank=True, null=True)
    max_memory = models.IntegerField(blank=True, null=True)
    comment = models.TextField(blank=True, null=True)
    fail_type = models.SmallIntegerField(blank=True, null=True)
    id = models.AutoField(unique=True, primary_key=True)
    error_text = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'test_info'
        unique_together = (('suite', 'run', 'test'),)

class ToolInfo(models.Model):
    id = models.IntegerField(primary_key=True)
    tool = models.ForeignKey(Tool, models.DO_NOTHING, blank=True, null=True)
    revision = models.ForeignKey(Revision, models.DO_NOTHING, blank=True, null=True)
    test_info = models.ForeignKey(TestInfo, models.DO_NOTHING, blank=True, null=True)
    note = models.TextField(blank=True, null=True)
    cl_id = models.IntegerField(blank=True, null=True)
    last_pass_cl_id = models.IntegerField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'tool_info'
        unique_together = (('tool', 'revision', 'test_info'),)

class SuiteInfo(models.Model):
    suite = models.ForeignKey(Suite, models.DO_NOTHING, primary_key=True)
    run = models.ForeignKey(Run, models.DO_NOTHING)
    owner = models.ForeignKey(Owner, models.DO_NOTHING)
    total = models.IntegerField()
    passing = models.IntegerField()
    date = models.DateTimeField(blank=True, null=True)
    reviewed = models.TextField(blank=True, null=True)
    comments = models.TextField(blank=True, null=True)
    orig_passing = models.IntegerField(blank=True, null=True)
    summary = models.ForeignKey('Summary', models.DO_NOTHING, blank=True, null=True)
    session_dir = models.TextField(blank=True, null=True)
    early_total = models.IntegerField(blank=True, null=True)
    date_finished = models.DateTimeField(blank=True, null=True)
    id = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'suite_info'
        unique_together = (('suite', 'run'),)

I'd like to write the following query using the django ORM. I'm using 2.2.

select test.path, tool_info.id, run.id, test_info.id, suite_info.id from run join test_info on run.id = test_info.run_id join suite_info on run.id = suite_info.run_id join tool_info on tool_info.test_info_id=test_info.id join test on test_info.test_id=test.id where run.id=34;

I've tried variations of this:

Run.objects.filter(id=tool_info_id).select_related('suiteinfo_set')
*** django.core.exceptions.FieldError: Invalid field name(s) given in select_related: 'suiteinfo_set'. Choices are: build, type, ko_type, branch, arch, kitinfo

I can use prefetch_related, but that doesn't give the all the data in one spot.

the RUN table is the central table that ties the data together. How can I create that query using the ORM?

Answer 1

You can include it in the SELECT part with .prefetch_related(…) ^[Django-doc]:

Run.objects.filter(suiteinfo__run_id=34).prefetch_related('suiteinfo__run')