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!

I don't want to write a new answer, but would add a link to the docs: http://docs.djangoproject.com/en/dev/topics/db/queries/#lookups-that-span-relationships for info on django JOINs specifically it will be helpful to learn how chaining filter() affects your m2m query — Yuji 'Tomita' Tomita, Feb 15 '11 at 03:50

score 9 · Accepted Answer · answered Feb 15 '11 at 03:41

9

Simply write something like:

Toy.objects.filter(toy_owners__parents=parent)

answered Feb 15 '11 at 03:41

Arnaud

1,785
18
22

what _owners and __parents mean? – Harun-Ur-Rashid Feb 09 '17 at 04:45
@Harun-Ur-Rashid It's been a long time since you asked your question, so I hope you found it or figured it out already, but for others who might have the same question: `_owners` is incomplete by itself; `toy_owners` is specified by the OP as the `related_name` for the `toys` field on the `Child` class. As for `__parents`, the `__` tells Django to reference a related object, in this case, `parents`, which is the `related_name` for the `children` field on the `Parent` class. – hlongmore Jan 11 '19 at 07:18

score 5 · Answer 2 · edited May 23 '17 at 12:15

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

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

2 Answers2

Linked