How to make an Inner Join in Django

Problem

Inner join is needed when you want to retrieve data that contains value on both sides. We can easily achieve this with Django ORM. In Django, there is a method called select_related that will allow us to make inner-join. Here our goal is to select and display the name of the city, state, and country of a publication but all of them reside at different tables. I am going to show how you can easily solve this problem.

In models.py

class country(models.Model): 
    country_name = models.CharField(max_length=200, null=True) 
    country_subdomain = models.CharField(max_length=3, null=True) 
    def __str__(self): 
        return self.country_name

class countrystate(models.Model): 
    state_name = models.CharField(max_length=200, null=True) 
    country = models.ForeignKey(country, on_delete=models.CASCADE, null=True) 
    importance = models.IntegerField(null=True) 
    def __str__(self): 
        return self.state_name

class city(models.Model): 
    city_name = models.CharField(max_length=200, null=True) 
    countrystate = models.ForeignKey(countrystate, on_delete=models.CASCADE, null=True) 
    def __str__(self): 
        return self.city_name

class publication(models.Model): 
    user = ForeignKey(users, on_delete=models.CASCADE, null=False) 
    title= models.CharField(max_length=300, null=True) 
    country=models.ForeignKey(country, on_delete=models.CASCADE, null=True) countrystate=models.ForeignKey(countrystate, on_delete=models.CASCADE, null=True) 
    city=models.ForeignKey(city, on_delete=models.CASCADE, null=True) 
    def __str__(self): 
        return self.title

In views.py

def publications(request) :
    mypublications = publication.objects.filter(user_id=request.session['account_id'])
    dic.update({"plist": mypublications }) 
    return render(request, 'blog/mypublications.html', dic)

Here ‘publication’ table doesn’t contain the information we need. So, we need to perform inner join and make relationships among the tables.

Solution (Recommended)

With the help of the select_related method, we will join the tables this way.

publications = publication.objects.select_related('country', 'country_state', 'city')

This will make the relationship we needed. If you want to join more tables then you can add the table name followed by a comma inside the bracket. Now you can loop through the publications to retrieve or display the required information this way.

{% for p in publications %} 
     {{ p.city.city_name}}
     {{ p.country.country_name}}
     {{ p.countrystate.state_name}}
{% endfor %}

This will now return the city name, country name, and state name of the publication.

 

Alternative Solution

In this case, we can display all the required information without using the inner join also. Although this method is not recommended it will do the job. Here in views.py, our Queryset result is set by the dictionary dic['plist'] . Now you can loop through this ‘plist’ like this.

{% for p in plist %} 
     {{ p.title }} 
     {{ p.city.city_name }} 
     {{ p.citystate.citystate_name }} 
     {{ p.country.country_name }} 
{% endfor %}

Here we didn’t need any join operation. It is recommended that you use the first solution’s method. Here we are just trying to display the result so it worked. Without displaying we may need to retrieve data from multiple tables for other tasks as well. In those cases, it is advised that you use join operations among the tables.

 

Hope this solved your problem. Don't forget to comment below if you face any more problems regarding this.