Combining 2 different tables

Using Django
#1

Hello I want to combine data of 2 different database tables, so I can access them using 1 context array. It should fetch those rows where the foreign key is equal to the primary key inside the other table.

My view code right now to give you a more clear understanding:

class AlgemeenOverzichtView(ListView):
    model = Service
    template_name = 'webapp/facturen/algemeenoverzicht.html'

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        context['service'] = Service.objects.all()
        context['service'] = Service.objects.select_related('contactNaam', 'bedrijfsNaam', 'adres')
        return context

This is what I’m currently doing, but I still can’t load in the data from the other table like this:

{% extends 'webapp/base/basedashboard.html' %} 
{% load static %} 

{% block content %}
{% include '../messages.html' %}
<!-- Begin Page Content -->
<div class="container-fluid">
  <!-- Page Heading -->
  <h1 class="h3 mb-2 text-gray-800">Algemeen Overzicht</h1>

  <!-- DataTales Example -->
  <div class="card shadow mb-4">
    <div class="card-header py-3">
      <h6 class="m-0 font-weight-bold text-primary">Overzicht</h6>
    </div>

    <div class="card-body">
      <div class="col-md-3 mt-3 mb-3">
      <i class="fas fa-search"></i>
        <input
          id="searchInputFacturen"
          data-url="{% url 'algemeenoverzicht' %}"
          class="form-control"
          name="q"
          type="text"
          placeholder="Zoeken op referentie..."
        />
      </div>
      <div class="table-responsive">
        <table
          id="algemeenOverzicht"
          class="table table-bordered table-sm"
          width="100%"
          cellspacing="0"
        >
          <thead>
            <tr>
              <th>Ref.</th>
              <th>Bedrijfsnaam</th>
              <th>Contactnaam</th>
              <th>Adres</th>
              <th>Leveringsdatum</th>
              <th>Ophalingsdatum</th>
              <th>WegingLevering</th>
              <th>WegingOphaling</th>
              <th>Aantal Boxen</th>
              <th>Aantal Rolcontainers</th>
              <th>Aantal Paletten</th>
              <th>Aantal Dozen Cartridges</th>
              <th>Afrekening</th>
              <th>Acties</th>
            </tr>
          </thead>

          <tbody id="fbody">
            {% for item in object_list %}
            <tr>
              <td class="referentie">
                <a href="{% url 'invoicecreate' item.id %}">
                    {{ item.referentie }}
                    <i class="fas fa-file-download"></i>
                </a>
              </td>
              <td>{{ item.bedrijfsNaam }}</td>
              <td>{{ item.contactNaam }}</td>
              <td>{{ item.adres }}</td>
              <td>{{ item.leveringsDatum }}</td>
              <td>{{ item.ophalingsDatum }}</td>
              <td>{{ item.wegingLevering }}</td>
              <td>{{ item.wegingOphaling }}</td>
              <td>{{ item.aantalBoxen }}</td>
              <td>{{ item.aantalRolcontainers }}</td>
              <td>{{ item.aantalPaletten }}</td>
              <td>{{ item.aantalDozenCartridges }}</td>
              <td>{{ item.afrekening }}</td>
              <td>
                <a href="{% url 'factuurupdaten' item.id  %}"
                  ><i class="fas fa-edit"></i
                ></a>
                <a
                  href="#"
                  data-url="{% url 'factuurverwijderen' item.id %}"
                  class="deletefactuur"
                  data-bs-toggle="modal"
                  data-bs-target="#dynamic-modal"
                  ><i class="fas fa-trash-alt"></i
                ></a>
              </td>
            </tr>
            {% endfor %}
          </tbody>
        </table>
      </div>
    </div>
  </div>

  {% endblock %} {% block footer %}
  <div class="container my-auto">
    <div class="copyright text-center my-auto">
      <span>Copyright &copy; Your Website 2020</span>
    </div>
  </div>
  {% endblock %}
</div>

‘bedrijfsNaam’, ‘contactNaam’ and ‘adres’ are columns of the other table that should be shown. But they currently do not get shown.

Any help is appreciated!

#2

It might help if you post the models involved - that way we can be sure we specify the correct field names.

But in general, you connect through foreign key relationships using the “object reference” notation - so it would be something like myobject.foreign_key_field.related_table_field. The select_related clause does not bring the data into the current objects, it constructs the query such that those related tables are retrieved using the same query as the base object. (Avoiding the n+1 problem)

Also:

This does you no good. The queryset returned by the first statement is overridden by the queryset being returned by the second statement. The first statement can be removed.

1 Like