How to avoid duplicates when Prefetch m2m related objects with soft deletable models?

I want to get list of accounts with not deleted relations.

Models

class User(models.Model):
    accounts = models.ManyToManyField(
        to='billing.Account',
        through='custom_auth.UserAccount',
        through_fields=('user', 'account'),
        related_name='users',
    )
    deleted = models.DateTimeField(
        verbose_name=_('Deleted at'),
        blank=True,
        null=True,
        db_index=True
    )

    objects = UserQuerySet.as_manager()

class UserAccount(models.Model):
    user = models.ForeignKey(
        to='custom_auth.User',
        on_delete=models.CASCADE)
    account = models.ForeignKey(
        to='billing.Account',
        blank=True,
        null=True,
        on_delete=models.CASCADE)
    deleted = models.DateTimeField(
        verbose_name=_('Deleted at'),
        blank=True,
        null=True,
        db_index=True
    )

class Account(models.Model):
    _users = models.ManyToManyField('custom_auth.User', blank=True)

User Manager

class UserQuerySet(models.QuerySet):
        def prefetch_accounts_for_api(self, request):
            accounts_qs = Account.objects.all()
            user_account_qs = UserAccount.objects.filter(
                user=request.user, 
                account_id=OuterRef('pk'), 
                deleted__isnull=True
            )[:1]
            accounts_qs = accounts_qs.filter(
                useraccount=user_account_qs
            )

            return self.prefetch_related(Prefetch(
                'accounts',
                queryset=accounts_qs,
                to_attr='enabled_accounts'
            ))

The problem is that when there are two rows in useraccount table (1 deleted and 1 not deleted) and i execute the query:

User.objects.all().prefetch_accounts_for_api(request)

User have duplicate of not deleted account relation in enabled_accounts attribute.

How can i get only one actual account in enabled_accounts?

Using PostgreSQL and Django 3.1.7

Back to Top