Django Query Set to get Row value as column name

Question

These are my models:

class Build(models.Model):
    name = models.CharField(db_index=True, max_length=56)
    description = models.TextField(max_length=512, null=True, blank=True)


class Case(models.Model):
    name = models.CharField(db_index=True, max_length=255)
    description = models.TextField(max_length=1024, null=True, blank=True)


class Result(models.Model):
    status = models.CharField(max_length=12)
    result_build = models.ForeignKey(Build, related_name='result_build', on_delete=models.CASCADE)
    result_case = models.ForeignKey(Case, related_name='result_case', on_delete=models.CASCADE)

I need a django QuerySet to get data like below:

....................................................
: case_name : case_description : build_X : build_Y :
:...........:..................:.........:.........:
:  test1    :   case1          :  PASS   :  FAIL   :
:  test2    :   case2          :  FAIL   :  PASS   :
:...........:..................:.........:.........:

where case_name and case_description are fields from Case model.

build_X and build_Y are the two build names available in Build model and PASS and FAIL are the status for different cases and builds from Result model.

Answer 1

This is a classical case for many-to-many relationship with a through model. The model classes can be refactored as following:

class Build(models.Model):
    name = models.CharField(db_index=True, max_length=56)
    description = models.TextField(max_length=512, null=True, blank=True)


class Case(models.Model):
    name = models.CharField(db_index=True, max_length=255)
    description = models.TextField(max_length=1024, null=True, blank=True)
    builds = models.ManyToManyField('Build', through='Result', related_name='cases')


class Result(models.Model):
    status = models.CharField(max_length=12)
    build = models.ForeignKey('Build', on_delete=models.CASCADE)
    case = models.ForeignKey('Case', on_delete=models.CASCADE)

We can also call the properties of Result simply build and case, without redundancy in the names. Although related_name doesn't bother, we don't really need it here.

Now you can use the m2m relationship to query your models:

case = Case.objects.get(pk=1) # get Case object with primary key 1
case.builds.all() # get all Build objects related to case
build = Build.objects.get(pk=1) # get Build object with primary key 1
build.cases.all() # get all Case objects related to build
# UPDATE
# get Result objects for the "case" with pk=1 retrieved before
results = Result.objects.filter(case=case)
# output each entry as row with all property values
for r in results:
    print(r.case.name, r.case.description, r.build.name, r.build, description, r.status)

You can also use .filter to narrow down your query result.

EDIT:

Here is one possible way to create a matrix table. This is a code you can put in your view:

cases = Case.objects.all()
builds = Build.objects.all()
matrix = []
for c in cases:
    result = {}
    result['case'] = c
    result['status'] = []
    for b in builds:
        result['status'].append(Result.objects.filter(
                                    case=c,
                                    build=b
                               ).values_list('status', flat=True))
    matrix.append(result)

Now you should have a table with a dictionary for every case. Pass builds and matrix as context to your template. Then you can iterate over the builds to create the table header (take care to leave space for a column or two in the beginning for listing the cases). Then iterate over the matrix and create the table body. First get the case for the first column (or the first two columns) and then output the status.

I hope this can point you the way. Once you have the right result you can further optimize the performance.

EDIT 2:

Here is an example how the table could look like.
Pass builds and matrix from the code snippet above as context to the template:

<table>
  <tr>
    <th>Case name</th>
    <th>Case description</th>
    {% for b in builds %}
      <th>{{ b.name }}</th>
    {% endfor %}
  </tr>
  {% for row in matrix %}
    <tr>
      <td>{{ row.case.name }}</td>
      <td>{{ row.case.description }}</td>
      {% for s in row.status %}
        <td>{{ s.0 }}</td>
      {% endfor %}
    </tr>
  {% endfor %}
</table>

In the first for loop we create a table header with two column headers for case name and case description and one column header for each build.
In the second for loop we create a table row for each case. In the nested loop we output the status.

It is a very simple approach and probably can be further optimized, but I leave that to you.