CODEWITHSUNDEEP
pythondjangopostgresql
GrineUlf
GrineUlf

Reputation: 15

Combining data from three tables in one view in Django

I have done a lot of searches and found some amazing answers on this topic for the last couple of days while I try to figure this out. But none of those worked for me, as it seems the situation is different than the earlier posted answers, or not working with my version of Django. So here goes:

I need to get data from 3 tables and displayed certain columns on a webinterface. I have no problem getting one table, but the other two (linked by foreign keys) I can't seem to get into the view. The code I have is as follows (mind you, I am learning python and django):

models.py:

class students(models.Model):
    date_created = models.DateTimeField()
    project = models.ForeignKey('projects',models.SET_NULL,blank=True,null=True,)
    name = models.TextField()
    return_date = models.TextField()


class sysinfo(models.Model):
    student = models.ForeignKey('students',models.SET_NULL,blank=True,null=True,)
    machine = models.TextField(blank=True, null=True)
    version = models.TextField(blank=True, null=True)
    platform = models.TextField(blank=True, null=True)
    hostname = models.TextField(blank=True, null=True)

class projects(models.Model):
    project_name = models.TextField()
    date_created = models.DateTimeField()
    date_closed = models.DateTimeField(blank=True, null=True)

views.py

from django.shortcuts import render
from front.models import students, projects, sysinfo

# Create your views here.
def index(request):
    list_students = students.objects.select_related('projects','sysinfo').order_by('-last_seen')
    return render(request, 'main/home.html',{'students': list_students})

This shows the student id and the date created and return date on the webinterface, but I can't seem to figure out to get the project name from the projects table, or the system information from sysinfo.

I have tried adding .values('projects__project_name','sysinfo__hostname'...etc.) to the views.py select_related line. But I only get errors saying that my choices are the fields in the students table.

I have lost track of everything I tried, but safe to say, the documentation by now is really not making any sense to me anymore. (The fact I create an account for SO just to ask this question should say plenty :P)

Thanks in advance all

Upvotes: 0

Views: 109

Answers (1)

bruno desthuilliers
bruno desthuilliers

Reputation: 77892

First let's fix the query. You want to use select_related for projects and prefetch_related for sysinfo (note that with your model a student has zero, one or many sysinfo). Also you want to use the foreignkey field name for selected_related and the reverse relationship name (in your case the default sysinfo_set) for sysinfo. And finally, you have no last_seen field in your students model...

def index(request):
    list_students = students.objects.select_related('project').prefetch_related('sysinfo_set')

Now in your template:

<table>
  {% for student in students %}
  <tr>
    <td>{{ student.name }}</td>
    <td>{{ student.project.project_name }}</td>
    <td>
       {% for sysinfo in student.sysinfo_set.all %}
       {{ sysinfo.host_name }}
       {% endfor %}
    </td>
  </tr>
  {% endfor %}
</table>

As a side note: the Python convention is to use CamelCase for class names, and the Django convention is to use singular for models names, so your models should be named Student, Project and SysInfo.

Upvotes: 3

Related Questions