How to make an Inner Join in django?

Question

I want to show in an Html the name of the city, state, and country of a publication. But they are in different tables.

Here is my 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

Here is my 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)

In a django view, what is the equivalent of the next sql query?

SELECT p.user_id, p.title, c.cuntry_id, c.country_name, s.state_id, s.state_name, y.city_id, y.city_name FROM publication AS p
INNER JOIN country AS c ON c.id = p.country_id
INNER JOIN countrystate AS s ON s.id = p.countrystate_id
INNER JOIN city AS y ON y.id = p.city_id

Answer 1

Let me beginning by describing meanings of terms and get order going...

InnerJoin implies the "common" section between the two (or more) tables. Each one being performed one after the other, as your SQL query suggests.

going through your SQL query, you are looking at Publication as the main, all your query are foreign key within publication, giving you ENTIRE dataset.

if my understanding goes correctly what you are looking for in Django equivalent is filter, in chain (not Query), because Q will give you results from each Q separately and join them, while you want results from one applied onto another.

(I dont know what the dic.update({"plist": mypublications }) does, its not clear.. Solution: country = country.objects.all() # gets all the country in the table of country. country_state = countrystate.objects.all() # get all the countrystate objects city = city.objects.all() # get all city objects

solution 1: in python3 you need to use __in, this works ok in Python2. and will get you country with anything from table country (not None), table countrystate (not None), table city (not None), so if anything from anyone of them, it would provide. Note: 'None' (python) = 'Null' (SQL Database) Getting all publications that are having anything from the above (practically will bring you anything. publications_list = publication.objects.filter(country = country, countrystate = country_state, city = city) # get if anyone from any of those where 'id' (based on which is object) match, which makes it innerjoin in itself.

Answer 2

You don't need an INNER JOIN nor select_related for this simple case, because you can traverse directly in template to the city, countrystate, and country – based on what I just experienced in my project.

Since your QuerySet is set via context dic['plist'], then, in template you can just:

{% for itero in plist %}

{{ itero.title }}
{{ itero.city.city_name }}
{{ itero.citystate.citystate_name }}
{{ itero.country.country_name }}

{% endfor %}

Answer 3

You are probably looking for select_related, which is the natural way to achieve this:

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

You can check the resulting SQL via str(pubs.query), which should result in output along the following lines (the example is from a postgres backend):

SELECT "publication"."id", "publication"."title", ..., "country"."country_name", ...  
FROM "publication" 
INNER JOIN "country" ON ( "publication"."country_id" = "country"."id" ) 
INNER JOIN "countrystate" ON ( "publication"."countrystate_id" = "countrystate"."id" ) 
INNER JOIN "city" ON ( "publication"."city_id" = "city"."id" ) 

The returned cursor values are then translated into the appropriate ORM model instances, so that when you loop over these publications, you access the related tables' values via their own objects. However, these accesses along the pre-selected forward relations will not cause extra db hits:

{% for p in pubs %}
     {{ p.city.city_name}}  # p.city has been populated in the initial query
     # ...
{% endfor %}

Answer 4

    from django.db import models

class Course(models.Model):
    name=models.CharField(max_length=10)
    courseid=models.CharField(max_length=30,primary_key=True)
class Enrollment(models.Model):
    course=models.ForeignKey(Course,on_delete=models.CASCADE)
    enrollid=models.CharField(max_length=20)

#Queryset:
k=Enrollment.objects.filter(course__courseid=1).values('id','course__courseid','course__name','enrollid')

print(k.query)
SELECT "app1_enrollment"."id", "app1_enrollment"."course_id", "app1_course"."name", "app1_enrollment"."enrollid" FROM "app1_enrollment" INNER JOIN "app1_course" ON ("app1_enrollment"."course_id" = "app1_course"."courseid") WHERE "app1_enrollment"."course_id" = 1