too many queries: populate template from model foreign key lookup
Gelonida N
I try to populate a template from a Model with a ForeignKey element)
members of the foreign key element should be displayed.
If I understand correctly my first attempt resulted in one query for
fetching the entries from my first model and in one Query for each row
in order to look up the contents related to the foreign key.
I try to explain with a simple example.
####### The Models ########################################
class Composer(models.Model):
last_name = models.CharField(max_length=20)
first_name = models.CharField(max_length=20)
class Song(models.Model):
title = models.CharField(max_length=20)
composer = models.ForeignKey(Composer)
############# My Task ##################
Now I want to populate a template with a list of songs and their composers
Intuitive lazy Solution with many queries
===========================================
The view
-----------
songs = Song.objects.all()
return render_to_response('mytemplate.html', {'song_list' : songs })
The template
---------------
{% for song in song_list %}
{{ song.title }} by {{ song.composer.last_name }},
{{song.composer.first_name }} <br/>
{% endfor %}
The problem
---------------
The problem here is, that django will perform one query to get the list
of songs and will then get perform one query per row in order to look up
the composer by his primary key.
I would prefer to have one query with a join
The verbose solution
=======================
I use values() in order to force the join upfront before rendiering in
the template
The view
-------------
songs = Song.objects.all().values('title', 'composer__last_name',
'composer__first_name')
return render_to_response('mytemplate.html', {'song_list' : songs })
The template
--------------
{% for song in song_list %}
{{ song.title }} by {{ song.composer__last_name }},
{{song.composer__first_name }} <br/>
{% endfor %}
The problem
-------------
This seems to do what I like, however there is one tiny problem.
I have to list all members of both models in the values section.
This seems to be rather verbose and repetetive.
My Questions
==============
1.) What is the recommended way to fill a template with values from a
model requiring one (or in my real example even two) joins.
2.) Is it possibe to perform a query and ask it to get ALL values from
the first model and to 'join' ALL values from a second model?
This would avoid having to manually retype all Model members in the
view-code
3.) Is there a way to perform a query with a join, but to receive
an object for each row (instead of receiving a dict with the 'flattened'
values containing double underscores as separators)
This would allow me to still use 'song.composer.last_name' in the
template but to avoid one query per template row
Thanks a lot for your suggestions or for clarifying my potential
misunderstandings of how Django works is supposed to be used.
Marc Aymerich
Hi,
I try to populate a template from a Model with a ForeignKey element)
members of the foreign key element should be displayed.
If I understand correctly my first attempt resulted in one query for
fetching the entries from my first model and in one Query for each row
in order to look up the contents related to the foreign key.
--
Marc
Gelonida N
This solved my issue and transformed my multiple queries into one
query with a join statement.
It is interesting to know however, that with my real code.
(slightly more complex models)
Song.objects.all()select_related()
did not have the desired effect.
I had to specify which foreign key to follow to make it work
so I had to use
Song.objects.all()select_related('composer')
Thanks again for the fast help