Django ORM way of going through multiple Many-to-Many relationship

Question

Dear people trying to help others,

I am trying to figure out how to get Django to do a join for me without writing custom SQL.

Let's say I have the following models

class Parent(models.Model): 
  name =  models.CharField()
  children = models.ManyToManyField(Child, through="Parent_Child", related_name="parents")

class Parent_Child(models.Model):
  parent = models.ForeignKey(Parent, related_name='attached_children')
  child = models.ForeignKey(Child,  related_name='attached_parents')

class Child(models.Model): 
  name = models.CharField() 
  toys = models.ManyToManyField(Toy, hrough="Child_Toy", related_name="toy_owners")

class Child_Toy(models.Model): 
  child = models.ForeignKey(Child, related_name='attached_toys') 
  toy =  models.ForeignKey(Toy, related_name='toy_owner')

class Toy(models.Model): 
  name = models.CharField

A parent can have multiple children. A child can have multiple parents. A child can own multiple toys. Toys can be owned by multiple children.

I want to get a list of all toys owned by a Parent's Children.

So, I can do things like: parent.children.all() and child.toys.all()

what I want to do is something like parent.children.toys.all() When I try to do this I get: AttributeError: 'ManyRelatedManager' object has no attribute 'toys'. I do understand the error - parent.children returns multiple records. This is expected. What I can't figure out is how to give Django the hint that I want it to add an additional join to its query.

Is there a way I can do this join within Django or do I need to go to custom SQL in order to do this?

Please Note: The above is just meant to illustrate my issue, the actual models that I am using aren't that relevant. My issue is really trying to figure out how to join through multiple M2M relationships in Django without having to resort to SQL.

I appreciate your help in advance. Thanks!

Answer 1

Simply write something like:

Toy.objects.filter(toy_owners__parents=parent)

Answer 2

If you don't store extra information in the intermediate tables Parent_Child and Child_Toy you can just leave them out - Django will create them automatically for you. So a simplified setup would look like this:

class Parent(models.Model): 
    name =  models.CharField(max_length=80)
    children = models.ManyToManyField('Child', related_name="parent")

class Child(models.Model): 
    name = models.CharField(max_length=80) 
    toys = models.ManyToManyField('Toy', related_name="owner")

class Toy(models.Model): 
    name = models.CharField(max_length=80)

You can query the toys for a specific parent by using field lookups.

Toy.objects.filter(owner__parent__id=1)

Or:

Toy.objects.filter(owner__parent=parent)

The resulting SQL looks something like this:

SELECT "toy"."id", "toy"."name" FROM "toy" 
    INNER JOIN "child_toys"
        ON ("toy"."id" = "child_toys"."toy_id") 
    INNER JOIN "child"
        ON ("child_toys"."child_id" = "child"."id") 
    INNER JOIN "parent_children" 
        ON ("child"."id" = "parent_children"."child_id")
    WHERE "parent_children"."parent_id" = 1