issue-detail: matrix of Tree x Nth checkout (cell links to checkout)

[tales-aparecida]

This is just an idea, but I wonder how an issue with multiple trees like https://d.kernelci.org/issue/maestro:96417cd30041ab8f3153128d0120c6cd70782d99?iv=1

would look in a matrix built from something like

# context: issue

from django.db.models import Exists, OuterRef, F, Window
from django.db.models.functions import Cast, RowNumber
from django.db.models import DateField

# 1. Get affected trees (same as before)
affected_trees = (
    issue.incidents_set
    .order_by("build__checkout__tree_name")
    .values_list("build__checkout__tree_name", flat=True)
    .distinct()
)

# 2. Query checkouts and calculate the sequential Nth checkout per day
grid_data = (
    Checkouts.objects.filter(
        start_time__gte=issue.first_seen,
        start_time__lte=issue.last_seen,
        tree_name__in=affected_trees
    )
    .annotate(
        date=Cast("start_time", DateField()),
        # Calculate Nth checkout per tree, per day
        checkout_index=Window(
            expression=RowNumber(),
            partition_by=[F("tree_name"), Cast("start_time", DateField())],
            order_by=F("start_time").asc()
        )
    )
    .annotate(
        has_incident=Exists(
            Incidents.objects.filter(
                issue=issue,
                build__checkout=OuterRef("id")
            )
        )
    )
    # Convert to values so the Window function evaluates cleanly into a dict
    .values("id", "tree_name", "date", "checkout_index", "has_incident")
    .order_by("tree_name", "date", "checkout_index")
)

# ---

from collections import defaultdict

# Initialize a structure: table[tree_name][date_index_label] = has_incident
table_matrix = defaultdict(dict)

# We also keep track of all unique labels seen to use as your table headers
all_column_headers = set()

for entry in grid_data:
    tree = entry["tree_name"]
    date_str = entry["date"].strftime("%Y-%m-%d")
    index = entry["checkout_index"]
    
    # Create your custom key string
    column_label = f"{date_str} - #{index}"
    
    # Store it in the matrix
    table_matrix[tree][column_label] = entry["has_incident"]
    all_column_headers.add(column_label)

# Sort headers chronologically & sequentially for your frontend table columns
sorted_headers = sorted(list(all_column_headers))

To achieve something like

Tree Name	2026-06-24 - #1	2026-06-24 - #2	2026-06-25 - #1
Tree X	✅ Pass	❌ Incident	✅ Pass
Tree Y	✅ Pass	N/A	❌ Incident

[alanpeixinho]

I dont know how big of an N we are thinking, but if we want many points of comparison, it might be the case for some sanken plot, or other visualization tool.

Allowing to click on subsets of tests.

[tales-aparecida]

sanken plot

so pretty!