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

Using Django Using the ORM
1

Hi!

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

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:

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?

2

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.)

3

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).

4

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.

5

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:

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
6

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.)