Skip to content
Advertisement

Django join tables with ForeignKey

I’m trying to join 3 tables with ForeignKey but it returns Null values. I’m using select related and also I tried Insight.objects.all() but both are not working.

Here are my models:

class schedule(models.Model):
    login_id = models.CharField(primary_key=True,max_length=20)
    agent_name = models.CharField(max_length=100, blank=True, null=True)
    team = models.CharField(max_length=100, blank=True, null=True)

class place_no(models.Model):
    place_id = models.CharField(max_length=50, blank=True,     null=True)
    pc_no =  models.CharField(max_length=50, blank=True, null=True)

class Insight(models.Model):
    login_id = models.CharField(primary_key=True,max_length=20)
    place_id = models.CharField(max_length=50, blank=True, null=True)
    agent_name = models.ForeignKey(
        schedule, on_delete=models.CASCADE,blank=True,
        null=True
    )
    pc_no = models.ForeignKey(
        place_no, on_delete=models.CASCADE,blank=True, null=True
    )

My View:

def listings(request):
data = Insight.objects.select_related('agent_name', 'pc_no')

return render(request, 'pages/listings.html', {'data':data})

{% for item in data %}
<tr>
<td class="text-center align-middle">{{ item.login_id }}</td>
<td class="text-center align-middle">{{ item.place_id }}</td>
<td class="text-center align-middle">{{ item.agent_name.agent_name }}</td>
<td class="text-center align-middle">{{ item.pc_no.pc_no }}</td>
</tr>
{% endfor %}

Advertisement

Answer

I solved my problem by the below, I could update the DB with the result of the below query

    cursor = connection.cursor()
    cursor.execute('''
    UPDATE Insight
        SET agent = ( SELECT agent_name FROM schedule
            WHERE Insight.login_id = schedule.login_id ) ''')
8 People found this is helpful
Advertisement