Optimal way to fetch related model and through model together in ManyToManyField

Using Django Using the ORM
1

Given schema:

class Investor(models.Model):
    name = models.CharField(max_length=30)
    advisors = models.ManyToManyField("Advisor", related_name="investors", through="Connection")

class Advisor(models.Model):
    name = models.CharField(max_length=30)

class Connection(models.Model):
    investor = models.ForeignKey("Investor", related_name="connections", on_delete=models.CASCADE)
    advisor  = models.ForeignKey("Advisor",  related_name="connections", on_delete=models.CASCADE)
    blocked  = models.BooleanField(default=False)

And given an Advisor “a”, what is the optimal to fetch a list of all Investors and their Connection to “a” when it exists?

The best I’ve figured out so far is:

from django.db.models import Prefetch

for investor in Investor.objects.prefetch_related(
    Prefetch(
        'connections',
        queryset=Connection.objects.filter(advisor=a),
        to_attr='connection',
    )
):
    name = investor.name
    blocked = None
    if investor.connection:
        blocked = investor.connection[0].blocked
    print(f"{name} (blocked={blocked})")
2

Update

I found a way to make using the above strategy more pleasant:

class WithConnection:
    def __init__(self, queryset):
        self.queryset = queryset

    def __iter__(self):
        for model in self.queryset:
            connection = model.my_connections[0] if model.my_connections else None
            yield model, connection

class InvestorQuerySet(models.QuerySet):
    def pov(self, advisor):
        return WithConnection(
            self.prefetch_related(models.Prefetch(
                'connections',
                queryset=Connection.objects.filter(advisor=advisor),
                to_attr='my_connections',
            ))
        )

Used like this:

advisor = Advisor.objects.first()
for investor, connection in Investor.objects.pov(advisor):
    # do stuff with investor
    if connection:
        # do stuff with connection

Would like to improve on this. Django’s ORM is so powerful, seems like there ought to be a simpler way to achieve this.

PS - I first tried subclassing ModelIterable and setting self._iterable_class to it in pov(), but Django would call my iterable on the models before during the second pre-fetch query, so my code failed when trying to access the my_connections attribute.

3

After much research and many iterations, I’ve arrived at this solution:

class WithConnectionQuerySet(models.QuerySet):
    def __iter__(self):
        return map(
            lambda m: (m, m.my_connections[0]) if m.my_connections else (m, None),
            super().__iter__(),
        )

class Investor(models.Model):
    ...
    @classmethod
    def viewed_by(cls, advisor):
        manager = cls._default_manager.__class__.from_queryset(WithConnectionQuerySet)()
        manager.model = cls
        return manager.prefetch_related(models.Prefetch(
            'connections',
            queryset=Connection.objects.filter(advisor=advisor),
            to_attr='my_connections',
        ))

Used like this:

advisor = Advisor.objects.first()
for investor, connection in Investor.viewed_by(advisor).filter(age__gt=40):
    # do stuff with investor
    if connection:
        # do stuff with connection