left join in queryset

Using Django
1

I’m trying to create a left join query
in models I defined two tables:

class Project(models.Model):
    title = models.CharField(max_length=88, null=True)
    person = models.ManyToManyField(User, through='subscription')

class Registration(models.Model):
    project = models.ForeignKey(Project, on_delete=models.CASCADE)
    user = models.ForeignKey(User, on_delete=models.CASCADE)

I want to show a list of all the projects to the person logged in and show him on which he has a subscription and on which not.

In SQL the query would look like this:

select p.*, r.user 
  from Project p
  left join Registration r
    on p.id = r.project_id
   and r.user_id = [request.user.id]

How do I create a query like this in Django?

2

To select on Project first then grab all the Registration records you would want prefetch_related possibly in combo with Prefetch

https://docs.djangoproject.com/en/3.2/ref/models/querysets/#prefetch-related

It’s probably better do drive your query from the Registration side though, using select_related …

https://docs.djangoproject.com/en/3.2/ref/models/querysets/#select-related

Registration.objects.select_related('Project').all()
3

Thanks for the reply, I will look into the docs!!
And for your suggestion to drive the query from the Registration side: then I will not have all the Projects, only the ones with a registration.

1 Like
4

This is true - it does depend on exact requirements, so you probably want a prefetch in that case.

5

I came up with this solution:

Project.objects.prefetch_related('person') \
    .filter(Q('registration__isnull=True) | Q(user=request.user.id)) \
    .values('id','title','registration__project')