Dry ORM

Code

Result

no cache

from django.db import models
from django.db.models import OuterRef, Subquery, Exists, Q

class Project(models.Model):
    name = models.CharField(max_length=100)

class Objective(models.Model):
    project = models.ForeignKey(Project, on_delete=models.CASCADE)
    title = models.CharField(max_length=100)

class Condition(models.Model):
    objective = models.ForeignKey(Objective, on_delete=models.CASCADE)
    level = models.ForeignKey("Level", on_delete=models.CASCADE)

class Level(models.Model):
    value = models.IntegerField()

class ProjectObjectiveCondition(models.Model):
    project = models.ForeignKey(Project, on_delete=models.CASCADE)
    objective = models.ForeignKey(Objective, on_delete=models.CASCADE)
    condition = models.ForeignKey(Condition, on_delete=models.CASCADE)
    status = models.CharField(max_length=2, blank=True)

def run():
    project = Project.objects.create(name="Alpha")
    objective = Objective.objects.create(project=project, title="Launch")
    l1 = Level.objects.create(value=1)
    l2 = Level.objects.create(value=2)
    l3 = Level.objects.create(value=3)

c1 = Condition.objects.create(objective=objective, level=l1)
    c2 = Condition.objects.create(objective=objective, level=l2)
    c3 = Condition.objects.create(objective=objective, level=l3)

ProjectObjectiveCondition.objects.bulk_create([
        ProjectObjectiveCondition(project=project, objective=objective, condition=c1, status="CA"),
        ProjectObjectiveCondition(project=project, objective=objective, condition=c2, status=""),
        ProjectObjectiveCondition(project=project, objective=objective, condition=c3, status="OK"),
    ])

undone_levels = ProjectObjectiveCondition.objects.filter(
        Q(status__in=["", "CA"]),
        project=project,
        objective=objective,
        condition__level__value=OuterRef("value")
    )

level_achieved = (
        Level.objects.filter(condition__objective=objective)
        .annotate(is_undone=Exists(undone_levels))
        .filter(is_undone=False)
        .order_by("value")
        .last()
    )

print("Level achieved:", level_achieved.value if level_achieved else None)

Output

No output.

Queries

Queries Q (0)

No queries.