CODEWITHSUNDEEP
pythondjangodjango-viewsforeign-keys
Undefined Variable
Undefined Variable

Reputation: 4267

Django - query multi-level foreign keys

I am trying to understand the concept of querying multiple level foreign key tables in Django, and I really hope someone can help me with it.

So, I am trying to create a List view in Django which shows players, their scores and some related information like their level of expertise. The database structure is something like below:

Players Table
-------------
id
name
<field 1>
<field 2>
<field N>

Then there is a table which stores the player's scores

Player Scores Table
-------------------
id
player_id (this refers to player.id field from table above)
score
level_id (this refers to a level.id field from table below)

And there is a level table like below:

Level Table
-----------
id
level_text

What I want is to create a list like below:

Player Name   Player Score    Player Level
-----------   -------------   ------------
Blue_Marvel   10000           Maestro
Man_At_Arms   51              Noob
P_K_In_Debt   1230000         God

As you can guess the data for each of these three columns need to come from three different tables.

I am very new to Django and can only perform some basic queries. I know I can do Player.object.all() and I get all the players (there is a player model defined). Similarly I can do Level.object.all() and I get all the expertise levels.

However I am not sure how I can proceed to construct a list such as above with player name, score and level text.

Any help is appreciated. Assuming that there is a Level and Score model, is there an easy way for me to create above list? If these models are not available, does Django query have any nifty way to achieve this?

I would really be grateful for any pointers!

Upvotes: 1

Views: 1259

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477160

For me, this looks like you want a ListView that actually maps on the PlayerScore table: for every row in the table there is one row in the output and vice versa.

The only think that makes this case more special, is that we want to fetch the player name as well. Typically you can do this by using a .annotate(..) on the values you want to select. So we can here define a ListView [doc]:

# views.py

from django.db.models import F

class PlayerScoreListView(ListView):

    model = PlayerScore
    template = '/app/playerscore_list.html'
    queryset = PlayerScore.objects.annotate(
        player_name=F('player__name'),
        level_name=F('level__name')
    )

So each time we will also fetch the player__name column, as well as the level__name. If you definedplayerandlevelasForeignKeys to thePlayermodel andLevelmodel respectively, Django will automatically make the JOINs at database level, and these are stored in thePlayerScoreinstances as.player_nameand.level_name` attribute.

The only thing we still need to do, is define a template. For example:

{% app/templates/app/playerscore_list.html %}

<html>
<body>
<table>
 <thead>
  <tr>
     <th>Player name</th>
     <th>Score</th>
     <th>Level name</th>
  </tr>
 </thead>
 <tbody>
 {% for item in object_list %}
  <tr>
     <td>{{ item.player_name }}</td>
     <td>{{ item.score }}</td>
     <td>{{ item.level_name }}</td>
  </tr>
 {% endfor %}
 </tbody>
</table>
</body>
</html>

Of course we also need to link a URL to this view:

# urls.py

from django.urls import path

from app.views import PlayerScoreListView

urlpatterns = [
    path('playerscore/', PlayerScoreListView.as_view(), name='playerscorelist'),
]

Of course the above is a "raw" implementation. In order to show all relevant content, and make the page look "pleasant", there still is some work to do.

Upvotes: 3

Related Questions