Left Outer Join and Child records of right table

Using Django Using the ORM
1

I have 3 models Category, Make and Variant connected as below

class Category:
      title = models.CharField(...

class Make:
      title = models.CharField(...
      category = models.ForeignKey(Category...

class Variant:
      title = models.CharField(...
      make = models.ForeignKey(Make....

Since I wanted to show all Categories and details of Make (available or None) for each Category, used a reverse lookup for LEFT OUTER JOIN like this:

Category.objects.annotate(make = FilteredRelation('make', condition=Q(...))).values('title', 'make')

which is working fine. However, I am not able to access the variant_set for each Make in the above query from Category model. I tried using prefetch_related but the join is not made as Category model will not know Variant model.

How to access count of variants from Category model for each Make?

Thanks

2

you can try using related name in each of Make and Variant classes Foreign keys

class Make:
      title = models.CharField(...
      category = models.ForeignKey(Category, related_name='category_make',.....

class Variant:
      title = models.CharField(...
      make = models.ForeignKey(Make, related_name='make_variant',....

Check this Methodology.
count_of_variant = category_object.category_make.make_variant.all()

3

I’m not sure I understand exactly what you want in your queryset.

A queryset for Category is going to annotate one value for each row of Category. But what you’ve written leads me to believe that you want all of the related Make objects annotated with the count of Variant related on each.

Are you looking to get all the Make objects for one Category object, with the count of Variant for each Make?

4

Yes exactly, and the output looks like this

Category      Make       Variants
---------------------------------
Category1    Make1              15
Category2    
Category3    Make2               5
Category4
Category5

Since all Categories may not have Make, used LEFT OUTER JOIN to show all Category objects and associated Make and count of variants.

Thanks

5

Django automatically creates a related_name using the name of model with the suffix _set. However I am not able to access the child_set of right table (Make model) in the above mentioned query.

6

I am able to do the same using raw SQL but not through ORM.

select A.title as Category, B.title as Make, count(C.id) as Variants 
from Category A left outer join Make B
on 
A.id = B.id
left outer join Variant C
on 
B.id = C.id 
group by A.title, B.title
7

I believe that to be correct.

I am not aware of any means to annotate a reference in a prefetch_related.

However, the issue with your construction as far as the ORM is concerned, is that you’re not showing the case where you have multiple Make for a single Category. (This is a many-to-one relationship, with Make on the “many” side.)

So what do you want to see if Category3 has both Make2 and Make3 related to it? If you want to see two separate rows for Category3, then the ORM is not going to provide a distinct “single-query” solution for you. You will need to issue two queries and join the results yourself. (Note: That’s actually what prefetch_related does. It reduces the number of queries from N+1 to 2, not N+1 to 1.)

8

For some reasons, I have ensured there is only one Make in a month, and filtering on month, a ForeignKey relation is just enough in my use case.

I have changed the UI by first display the Category-Make values, and using HTMX partials, loading the Make-Variant details.

Thanks

9

Is this not a common use case, when Django ORM actually allows to span relations to n levels?

10

Actually, I was wrong in my previous statement.

The Prefetch object exists to allow for that type of filtering in that prefetch_releated call.

See the complete docs for prefetch_related, specifically the section on Prefetch along with the docs at Prefetch.

Note that this still isn’t an outer join - it’s still a separate query per prefetch_related entry, but does provide the filtering option.