Django select_related query does not return all values to the template

Question

I have 2 models. Characters and CharactersDetails from a django site I'm fooling around with.

CharactersDetails has a foreignkey relationship to Characters

class CharactersDetails(models.Model):
    character_link = models.ForeignKey(Characters)

In my view, I'm doing this:

'character_info': CharactersDetails.objects.select_related('character_link').filter(character_link_id=pk)

In my template, I'm only getting back the fields from the CharactersDetails table.

In the manage.py shell, I ran the same query and found it's actually returning the fields from both CharactersDetails and Characters (which is what I want), but when the view gets the context object, it's only showing the items from the CharactersDetails table.

(looking_for_guild_env)klainn:~/workspace (master) $ python manage.py shell
Python 3.4.3 (default, Nov 17 2016, 01:08:31) 
>>> from players.models import Characters,CharactersDetails
>>> myjunk = CharactersDetails.objects.select_related('character_link').filter(character_link_id=1)
>>> print(myjunk)
<QuerySet [<CharactersDetails: CharactersDetails object>]>
>>> print(myjunk.query)
SELECT "characters_details"."id", "characters_details"."character_link_id", "characters_details"."character_class_id", "characters_details"."character_race_id", "characters_details"."character_level", "characters_details"."character_armory_url", "characters_details"."character_profile_image_url", "characters_details"."character_profile_avatar_url", "characters_details"."character_profile_inset_url", "characters"."id", "characters"."character_owner_id", "characters"."character_name", "characters"."character_realm_id", "characters"."character_faction_id", "characters"."insert_date" FROM "characters_details" INNER JOIN "characters" ON ("characters_details"."character_link_id" = "characters"."id") WHERE "characters_details"."character_link_id" = 1
>>>

I take that same query and paste it into the manage.py dbshell:

(looking_for_guild_env)klainn:~/workspace (master) $ python manage.py dbshell
sqlite> SELECT "characters_details"."id", "characters_details"."character_link_id", "characters_details"."character_class_id", "characters_details"."character_race_id", "characters_details"."character_level", "characters_details"."character_armory_url", "characters_details"."character_profile_image_url", "characters_details"."character_profile_avatar_url", "characters_details"."character_profile_inset_url", "characters"."id", "characters"."character_owner_id", "characters"."character_name", "characters"."character_realm_id", "characters"."character_faction_id", "characters"."insert_date" FROM "characters_details" INNER JOIN "characters" ON ("characters_details"."character_link_id" = "characters"."id") WHERE "characters_details"."character_link_id" = 1;
1|1|15|7|110|http://us.battle.net/wow/en/character/Stormrage/Peppiwyn/simple|stormrage/219/182881755-profilemain.jpg|stormrage/219/182881755-avatar.jpg|stormrage/219/182881755-inset.jpg|1|3|Peppiwyn|192|0|2017-03-20

And I get all fields. But the template doesn't get passed the last 6 values. I could see this when I add a .values() to the end of the queryset and then do a:

{{ character_info }}

in the template I see:

<QuerySet [{'id': 1, 'character_profile_image_url': 'stormrage/219/182881755-profilemain.jpg', 'character_race_id': 7, 'character_level': 110, 'character_profile_avatar_url': 'stormrage/219/182881755-avatar.jpg', 'character_profile_inset_url': 'stormrage/219/182881755-inset.jpg', 'character_armory_url': 'http://us.battle.net/wow/en/character/Stormrage/Peppiwyn/simple', 'character_link_id': 1, 'character_class_id': 15}]>

Have I done something wrong or is there a special way I should be calling these values from the template?

Thanks for reading.

score 2 · Accepted Answer · answered Mar 23 '17 at 15:42

The queryset method .values() does not add implicitely the attributes of the foreign keys selected. Here is an extract of the documentation:

A few subtleties that are worth mentioning:

If you have a field called foo that is a ForeignKey, the default values() call will return a dictionary key called foo_id, since this is the name of the hidden model attribute that stores the actual value (the foo attribute refers to the related model). When you are calling values() and passing in field names, you can pass in either foo or foo_id and you will get back the same thing (the dictionary key will match the field name you passed in).

You need to list the fields as values('character_link__field1', 'character_link__field2'...) arguments... or just don't use it and process ORM objects, that will use the data retrieved in one query.

With tools like django-debug-toolbar, you can inspect your requests to ensure only one SQL query is made when you are rendering your template.

score 0 · Answer 2 · answered Mar 23 '17 at 15:40

Doing select_related('character_link') doesn't add the attributes to the character_info instance, it just fetches the character_info from the db at the same time as character_link, using an inner join.

In the template, you access the character_link instance {{ character_info.character_link }}.

If you are using values(), you have to explicitly include fields from character_link:

.values('field1', 'field2', 'character_link__field', ...)

Django select_related query does not return all values to the template

2 Answers2