Django - how to filter though multiple manyTomany relationship layers

Consider the following setup:

class ModelA(models.Model):
    foreign = models.ForeignKey(ModelB, on_delete=models.CASCADE)
    children = models.ManyToManyField('self', related_name="parent", symmetrical=False, blank=True)

class ModelB(models.Model):
    caption = models.CharField(db_index=True, max_length=50, null=False, unique=True)
    children = models.ManyToManyField(ModelC, blank=True)

class ModelC(models.Model):
    ...lots of fields

Now, given the pk of a ModelA Object, I want to get and filter all the related ModelC Objects. Here is what i'm trying to achieve efficiently:

modelC_objects = ModelA.objects.get(pk=modelA_id).children.foreign.children
    .filter(pk__lte=last_id)      
    .exclude(is_private=True)
    .order_by('-pk')[0:100]
    .prefetch_related("other")
)

Obviously that doesn't work. I am currently doing something ugly like this:

modelA_objects = ModelA.objects.get(pk=modelA_id).children
modelC_querysets = [modelA.foreign.children for modelA in modelA_objects]
if modelC_querysets:
    modelC_objects = modelC_querysets[0]
    modelC_querysets.pop(0)
    for x in modelC_querysets:                    
        modelC_objects = modelC_objects | x
filtered = (modelC_objects.filter(pk__lte=last_id)      
   .exclude(is_private=True)
   .order_by('-pk')[0:100]
   .prefetch_related("other")
)

How can I achieve what I attempted?

Solution

You want to get ModelC objects, so you need to start your query on ModelC. But it would also help if you name the reverse relationships in your models so that it's easier to traverse in the opposite direction:

class modelA:
    foreign = models.ForeignKey(ModelB, related_name='modelAs' on_delete=models.CASCADE)
    ...

class modelB:
    children = models.ManyToManyField(ModelC, related_name='parents')
    ...

modelA_qs = ModelA.objects.filter(Q(id=pk) | Q(parents__id=pk))
modelC_objects = ModelC.objects.filter(parents__modelAs__in=modelA_qs)

The first parents refers to the ModelB objects that are parents to a ModelC object, then modelAs fetches the ModelA objects for each of them. You probably should add a distinct() clause at the end, because you'll very likely get duplicate modelC objects.