# Join three models with foreign keys B to A & C to A

**URL:** <https://forum.djangoproject.com/t/join-three-models-with-foreign-keys-b-to-a-c-to-a/24344>\
**Category:** Using the ORM\
**Created:** [October 6, 2023, 8:48am UTC](https://forum.djangoproject.com/t/join-three-models-with-foreign-keys-b-to-a-c-to-a/24344 "2023-10-06T08:48:17Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![CalvinMT](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/calvinmt/32/16294_2.png) [@CalvinMT](https://forum.djangoproject.com/u/CalvinMT)\
**Post date:** [October 6, 2023, 8:48am UTC](https://forum.djangoproject.com/t/join-three-models-with-foreign-keys-b-to-a-c-to-a/24344/1 "2023-10-06T08:48:17Z")

</div>

Hi!

Using Django 4.2, I have three models looking as the following:

```python
class A(models.Model):
    # [...]

class B(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    a = models.ForeignKey(A, on_delete=models.CASCADE, null=True)
    # [...]
    class Meta:
        unique_together = (("user", "a"), )

class C(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    a = models.ForeignKey(A, on_delete=models.CASCADE, null=True)
    # [...]
    class Meta:
        unique_together = (("user", "a"), )

```

`B.user` and `C.user` are related.

What I want is to join those three models based on the `a` foreign keys and the `user` foreign keys so that I can access A, B and C fields with a single QuerySet in my templates.

If possible, I would also want to have B fields equal to null if `user` exists in C but not in B.

I have tried using `prefetch_related` in the following way, but it doesn’t seem to let me access B and C fields:

```python
result = A.objects.prefetch_related("b_set", "c_set").filter(b __user=user, c__ user=user)

```

I might need to use a subquery or an `annotate()`, but I really don’t understand how they work.

Would anyone, please, be willing to help me?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [October 6, 2023, 12:10pm UTC](https://forum.djangoproject.com/t/join-three-models-with-foreign-keys-b-to-a-c-to-a/24344/2 "2023-10-06T12:10:41Z")

</div>

Writing a query on `A` automatically gives you access to both sets of related objects.

If you have:  
`all_a = A.objects.all()`

Then, for any element of `all_a` which we’ll name `an_a`, `an_a.b_set.all()` is the set of all `B` related to `an_a`. Likewise, `an_a.c_set.all()` is the set of all `C` related to `an_a`.

The `prefetch_related` clause is not _required_ for access to those collections, it’s _only_ a performance enhancement. If you iterate over both `all_a` and subsequently reference the `b_set` and `c_set` collections in a tempate, you’re going to end up in a `2N+1` query situation - which is what `prefetch_related` prevents.

Now, when it comes to accessing those fields in `b_set` or `c_set`, keep in mind that you may have many `B` (or `C`) related to a single `A`.

> Side note: If that’s not the case - if there’s only 0 or 1 `B` that can be related to `A` - then you’ve designed your model incorrectly - it would be a OneToOneField that is needed, not a Foreign Key.

That means that `an_a.b_set.all()` is a collection, and you would need to iterate over that collection to access the individual fields of the individual members of `b_set`.

The other way you may want to look at this is to realize that what you have effectively created are two separate ManyToMany relationships between `User` and `A`, with two `through` models. (I don’t know whether that helps you visualize what’s going on here or not, but sometimes changing your perspective on an issue helps.)

---

<div class="post-metadata">

**Author:** ![CalvinMT](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/calvinmt/32/16294_2.png) [@CalvinMT](https://forum.djangoproject.com/u/CalvinMT)\
**Post date:** [October 6, 2023, 2:12pm UTC](https://forum.djangoproject.com/t/join-three-models-with-foreign-keys-b-to-a-c-to-a/24344/3 "2023-10-06T14:12:56Z")

</div>

Thank you for your reply.  
I think that I understand all of it.

To bring more context, there can be many B and many C related to a single A. I am sure not to need OneToOneField instead of my ForeignKey fields.

I can see that I have access to the set of all B and to the set of all C related to each element of A. But that is not what I am looking for.

I do not want to iterate more than once in my template. That is why I am looking for a solution to obtain a QuerySet with the fields of all three models joined on A and User (which, I forgot to mention, are `unique_together` in models B and C).

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [October 6, 2023, 2:19pm UTC](https://forum.djangoproject.com/t/join-three-models-with-foreign-keys-b-to-a-c-to-a/24344/4 "2023-10-06T14:19:30Z")

</div>

> [@CalvinMT](#):
>
> I do not want to iterate more than once in my template.

Depending upon what _exactly_ you’re trying to generate, that may not be possible. (It would be helpful if you could provide a minimal example showing just a handful of rows of data along with what you’re trying to achieve. I’m not seeing what the output is that you’re trying to create here.)  
Keep in mind that regardless of the `unique_together` clause, something like `B.objects.filter(user=some_user, a=an_a)` is going to return a queryset and not an individual instance of B.

---

<div class="post-metadata">

**Author:** ![CalvinMT](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/calvinmt/32/16294_2.png) [@CalvinMT](https://forum.djangoproject.com/u/CalvinMT)\
**Post date:** [October 10, 2023, 8:46am UTC](https://forum.djangoproject.com/t/join-three-models-with-foreign-keys-b-to-a-c-to-a/24344/5 "2023-10-10T08:46:10Z")

</div>

I basically want to do a left join and access values of A, B and C through the same QuerySet.

For example, if my models were to be looking like this:

```python
class A(models.Model):
    name = ...
    moderator = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    start_date = ...
    end_date = ...

class B(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    a = models.ForeignKey(A, on_delete=models.CASCADE, null=True)
    first_date = ...
    last_date = ...

    class Meta:
        unique_together = (("user", "a"), )

class C(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    a = models.ForeignKey(A, on_delete=models.CASCADE, null=True)

    class Meta:
        unique_together = (("user", "a"), )

```

With values for these models looking like this:

| A | id | name | moderator | start\_date | end\_date |
| --- | --- | --- | --- | --- | --- |
| | 1 | name\_1 | 55 | sdate\_1 | edate\_1 |
| | 2 | name\_2 | 88 | sdate\_2 | edate\_2 |
| | 3 | name\_3 | 55 | sdate\_3 | edate\_3 |

| B | id | user | a | first\_date | last\_date |
| --- | --- | --- | --- | --- | --- |
| | 1 | 133 | 1 | fdate\_1 | ldate\_1 |
| | 2 | 133 | 2 | fdate\_2 | ldate\_2 |
| | 3 | 177 | 1 | fdate\_3 | ldate\_3 |

| C | id | user | a |
| --- | --- | --- | --- |
| | 1 | 133 | 1 |
| | 2 | 133 | 2 |
| | 3 | 177 | 1 |
| | 4 | 177 | 2 |
| | 5 | 177 | 3 |

I would want the resulting QuerySet to be structured as if it was retrieving its values from the following table:

| ABC | id | name | moderator | start\_date | end\_date | user | a | first\_date | last\_date |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| | 1 | name\_1 | 55 | sdate\_1 | edate\_1 | 133 | 1 | fdate\_1 | ldate\_1 |
| | 2 | name\_2 | 88 | sdate\_2 | edate\_2 | 133 | 2 | fdate\_2 | ldate\_2 |
| | 3 | name\_1 | 55 | sdate\_1 | edate\_1 | 177 | 1 | fdate\_3 | ldate\_3 |
| | 4 | name\_2 | 88 | sdate\_2 | edate\_2 | 177 | 2 | null | null |
| | 5 | name\_3 | 55 | sdate\_3 | edate\_3 | 177 | 3 | null | null |

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [October 10, 2023, 8:36pm UTC](https://forum.djangoproject.com/t/join-three-models-with-foreign-keys-b-to-a-c-to-a/24344/6 "2023-10-10T20:36:30Z")

</div>

Unfortunately, I’m not in a position where I can test this right now, but I’d be trying something like:

`all_c = C.objects.all().select_related('a').prefetch_related('a__b')`

If you iterate over `all_c`, then for each `c` the related fields of `A` would be `c.a.name`, `c.a.moderator`, etc.

The instance of `B` that you are then looking for would be `c.a.b_set.get(user=c.user)`, allowing you to access the fields as `c.a.b_set.get(user=c.user).first_date` and `c.a.b_set.get(user=c.user).last_date`

Rendering this in a template is going to require that you iterate over `all_c` in the view to collect the data into a single data structure before calling `render`. You’re not going to be able to do this directly in the template because of the need to pass a parameter to the `get` method.

Another approach you could take would be to annotate the fields of `B` on `C` as a subquery - either as a JSONField or as two separate subqueries, each annotating an individual field. (There’ve been a couple of different discussions here about this. Searching for `Subquery` is likely going to find some of them.)