How to get data from one Django model to query another model within a template loop?

Question

My Django application has two Models 'Items' & 'Colors' representing database tables 'items' and 'colors'. A Django template 'mytemplate.html' renders data gathered from the database with a 'for' loop, printing out a list of items with their properties.

One of the fields of 'items' table is a numeric id that correspond to a text field in 'colors' table. Currently I can display all the items with their names and their color numeric id 'cid' (see code below).

But I need to print the color name of an item instead of its 'cid'/'id' within the template loop. What is the most efficient way to achieve this? Do I need an intermediary data structure, alter my database to define a foreign key (items(cid) --> colors(id)), ... ?

I'm not sure that I want to use a foreign key (items(cid) --> colors(id)) because at the time of first insertion of items 'cid' could be undefined (NULL).

Table 'items'

+------+------+------+
| id   | cid  | name |
+------+------+------+
|  1   |   3  | barZ |
|  2   |   3  | barC |
|  3   |   1  | barE |
|  3   |   2  | barD |
|  4   |   1  | barA |
+------+------+------+

Table 'colors'

+------+---------+
| id   | name    | 
+------+---------+
|  1   |   red   | 
|  2   |   white | 
|  3   |   blue  | 
+------+---------+

models.py

from django.db import models

class Items(models.Model):
    cid = models.IntegerField(blank=True, null=True)
    name = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'items'

class Colors(models.Model):
    name = models.TextField(blank=False, null=False)

    class Meta:
        managed = False
        db_table = 'colors'

views.py

from django.shortcuts import render
from .models import Items
from .models import Colors

def item_list(request):

    items = Items.objects.all().order_by('id')
    colors = Colors.objects.all().order_by('name')

    return render(request,'mytemplate.html',{
        'items': items, 
        'colors': colors  
    })

mytemplate.html

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Foos</title>
</head>
<body>
{% block page_content %}
<table>
{% for item in items %}
    <tr>
        <td>{{ items.name }}</td>
        <td>{{ items.cid }}</td>
    </tr>
{% endfor %}
</table>
{% endblock %}
</body>
</html>

@ans2human: Good point, done. Any hints about the actual question? — donmelchior
– donmelchior, Commented Oct 4, 2019 at 13:28

carlesgg97 · Accepted Answer · 2019-10-04 11:28:10Z

1

You should use a foreign key. That foreign key can be set to be nullable, so it being set as NULL on creation is not a problem.

Afterwards, the color could easily be accessed from the template by doing something like {{ items.color.name }}

You can find more information on django model's foreign keys here.

answered Oct 4, 2019 at 11:28

carlesgg97

4,4861 gold badge11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

donmelchior Over a year ago

carlesgg97: 'id' colum of 'colors' table can't be null in my current implementation so defining 'cid' as a foreign key referencing colors(id) in 'items' table produces an error "ERROR 1005 (HY000): Can't create table 'mydb.#sql-573_19' (errno: 150). 'cid' can be undefined but colors(id) can't.

carlesgg97 Over a year ago

I have made it work with models declared as following: pastebin.com/J8Zm970L . How are you declaring your foreign key?

donmelchior Over a year ago

In raw SQL: ALTER TABLE items ADD FOREIGN KEY (cid) REFERENCES colors(id);

carlesgg97 Over a year ago

donmelchior: Make sure that the Item.cid column is nullable (it mustn't be declared as NOT NULL nor UNIQUE). For reference, you may take a look at the SQL create table sentences generated by django makemigrations: pastebin.com/dhT8mYC3

donmelchior · Accepted Answer · 2019-10-09 14:50:25Z

In this particular use case I can't use foreign key to create a relation between a nullable column (items 'cid') and a non nullable colum (colors 'id').

I created the relation between 'items' table data and 'colors' table data directly within the model using a method 'get_color' which is called later from the template:

models.py

from django.db import models

class Colors(models.Model):
    name = models.TextField(blank=False, null=False)

    class Meta:
        managed = False
        db_table = 'colors'

class Items(models.Model):
    cid = models.IntegerField(blank=True, null=True)
    name = models.TextField(blank=True, null=True)

    # New method to grap data from Colors model 
    def get_color(self):
        color_object = Colors.objects.get(id = self.cid)
        color = color_object.name
        return color

    class Meta:
        managed = False
        db_table = 'items'

views.py

from django.shortcuts import render
from .models import Items
from .models import Colors

def item_list(request):

    items = Items.objects.all().order_by('id')
    colors = Colors.objects.all().order_by('name')

    return render(request,'mytemplate.html',{
        'items': items
        # Note: Direct reference to 'colors' table is no longer needed here

    })

mytemplate.html

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Items</title>
</head>
<body>
{% block page_content %}
<table>
{% for item in items %}
    <tr>
        <td>{{ item.name }}</td>
        <!-- get_color method call from model Class instance -->
        <td>{{ item.get_color }}</td>
    </tr>
{% endfor %}
</table>
{% endblock %}
</body>
</html>

In many use cases setting a foreign key is a best practice as mentionned. But implementation shown above is usefull, especially if you want to perform additionnal data conversion. Note that you can't directly pass arguments to the method within the template call '<td>{{ item.get_color }}</td>'

Collectives™ on Stack Overflow

How to get data from one Django model to query another model within a template loop?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related