CODEWITHSUNDEEP
djangomany-to-manydjango-filter
mrj
mrj

Reputation: 629

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?

Upvotes: 2

Views: 2818

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477854

You can filter with:

ModelC.objects.filter(modelb__modela__parent=my_pk)

where modelb and modela are the names of the models in lowercase, or if you specified a related_query_name=.. or related_name=.. for the children of ModelB or foreign of ModelA respectively, use these. This will work with JOINs, which typically is what databases are good with.

This will however not walk the recursive structure of ModelA, it will thus go one level (the __parent lookup).

Upvotes: 0

dirkgroten
dirkgroten

Reputation: 20702

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.

Upvotes: 3

Related Questions