[Answer]-Joining three or more tables using django ORM

by

1👍

✅

You should use prefetch_related for efficiency, since your relation is reverse (you want to access the other records from User and not the other way around):

u = User.objects.prefetch_related('project_set', 'department_set', 'basicdetails_set').get(username='user1')

This will not generate a single query, but Django will use caching techniques to effectively produce less possible db overhead. If I recall correctly it will produce 4 queries (a single join in 4 tables might be slower anyway, depending on number of records, indexes etc). The benefit is that on subsequent requests no queres will be generated. For example to get a user’s projects:

u.project_set.all() #hits the db
u.project_set.all() #cached version, no db hit

For more info see here https://docs.djangoproject.com/en/dev/topics/db/queries/#one-to-one-relationships.

EDIT: what is project_set?

If your Project model is defined like this

class Project(models.Model):
    ...
    user = models.ForeignKey(User)

then you can do Project.objects.get(pk=1).user to access the user associated to a project instance, but how would you do the opposite (get all projects of a certain user)? Django will automatically include a ‘_set’ property to the other model for convenience. Therefore we can get the projects of a certain user like this:

u = User.objects.get(pk=1)
user_objects = u.project_set.all()

However if you want to explicitly set a name for this reverse relation, django allows you define the ForeignKey with a related_name keyword argument like this:

class Project(models.Model):
    ...
    user = models.ForeignKey(User, related_name='projects')

Now instead of .project_set you could use .projects to access a user’s projects:

u = User.objects.get(pk=1)
user_objects = u.projects.all()
👤ppetrid

Leave a comment