Django annotate foreign key challenge

models.py

from django.contrib.auth.models import User
from django.db import models
from django.db.models import Case, When, Q
from django.db.models.expressions import Value, F


class WidgetManager(models.Manager):
    def with_user_status(self, user: User = None):
        """This approach duplicates the same widget for each UserStatus"""
        return self.annotate(
            unified_status_id=Case(
                When(user_status__user=user, then='user_status__status_id'),
                default=F('status_id'),
                output_field=models.IntegerField(),
            )
        ).order_by('unified_status_id')

class Widget(models.Model):
    STATUS_NOK = 1
    STATUS_OK = 2
    STATUS_NONE = 3

    name = models.CharField(max_length=40)
    status_id = models.IntegerField(default=STATUS_NONE)

    objects = WidgetManager()

    def __str__(self):
        return f'{self.name} - status: {self.status_id}'


class UserStatus(models.Model):
    user = models.ForeignKey(to=User, on_delete=models.CASCADE)
    widget = models.ForeignKey(to=Widget, on_delete=models.CASCADE, related_name='user_status')
    status_id = models.IntegerField(default=Widget.STATUS_NONE)

    def __str__(self):
        return f'{self.widget.name} - status: {self.status_id}'

run.py

import django

django.setup()

from django.contrib.auth.models import User

from apps.core.models import Widget, UserStatus

Widget.objects.all().delete()
UserStatus.objects.all().delete()

u1 = User.objects.filter(username='user1').first() or User.objects.create_user('user1')
u2 = User.objects.filter(username='user2').first() or User.objects.create_user('user2')

w1 = Widget.objects.create(name='Widget1', status_id=Widget.STATUS_NONE)
w2 = Widget.objects.create(name='Widget2', status_id=Widget.STATUS_OK)
w3 = Widget.objects.create(name='Widget3', status_id=Widget.STATUS_NOK)

UserStatus.objects.create(user=u1, widget=w1, status_id=Widget.STATUS_NOK)
UserStatus.objects.create(user=u2, widget=w1, status_id=Widget.STATUS_OK)

qs = Widget.objects.all()
print(f'All widgets: {qs}')

qs = Widget.objects.with_user_status(user=u1)
print(f'Widgets with user status {[(w.name, w.unified_status_id) for w in qs]}')

https://stackoverflow.com/a/66465636/3824772