form doing a query for each element of foreign key in select

[Lee Hinde]

I have a table with four or five foreign keys. Using the default form
widgets, a foreign key is represented by a single value select.

I noticed, with debug_tool_bar, that a query is being made for each
element of each foreign key select option. Seems like it ought not

I am using

    classes = get_object_or_404(Classes, pk=pk)

to load the record. I tried
Classes.on_site.select_related().get(pk=pk) without any change.

The question is, is this to be expected?

I'm using a fairly plain form:

{% extends 'district/district_edit_base.html' %}
{% block title %}
{{block.super}}
{% endblock title %}
{% block extra_ready_js %}
    {{block.super}}
     $("#district_list").collapse('show');
{% endblock extra_ready_js %}

{% block content %}
<form id="simple_form" method="post"
action="/staff/admin/classes/save/{{ id }}" class="form-horizontal">
    {% include "simple_fields.html" %}
</form>
{% endblock content %}

simple_fields is

<fieldset xmlns="http://www.w3.org/1999/html">
{% csrf_token %}
{% for field in form %}
    <div class="control-group  {% if field.errors %} error {% endif%}">
        <label class="control-label"
for="id_{{field.name}}">{{field.label}}</label>
            <div class="controls">
              {{field}} <span class="field_help"> {{ field.help_text }}</span>
              <span class="help-inline">
                {{ field.errors }}
            </span>
            </div>
    </div>
{% endfor %}

<div id="submitButton" class="form-actions">
    {% if id != 0 %}
        <input type="hidden" name="Delete" id="real_delete" value="" />
        <input type="button" name="DeleteInit" id="DeleteInit"
value="Delete" class="btn btn-warning btn-delete" data-toggle="modal"
data-target="#modal-delete" data-backdrop="true" data-keyboard="true"
/>
    {% endif %}
    <span class="spacer">&nbsp;</span>
    <input type="submit" name="Submit" value="Save" class="btn btn-primary" />
    <span class="spacer">&nbsp;</span>

    <input type="button" name="return" id="return_to_list"
value="Return" title="Return to the list." class="btn" />
</div>
 </fieldset>

[Tom Evans]

On Wed, Apr 25, 2012 at 5:58 AM, Lee Hinde <leeh...@gmail.com> wrote:
> I have a table with four or five foreign keys. Using the default form
> widgets, a foreign key is represented by a single value select.
>
> I noticed, with debug_tool_bar, that a query is being made for each
> element of each foreign key select option. Seems like it ought not
>
> I am using
>
>     classes = get_object_or_404(Classes, pk=pk)
>
> to load the record. I tried
> Classes.on_site.select_related().get(pk=pk) without any change.
>
> The question is, is this to be expected?
>

Yes. How else would it get the potential values that can be selected?

If you want to load all in one query, you can use select_related(). If
select_related() doesn't help, then your foreign keys are probably
nullable, which are not automatically loaded with select_related().

FYI, having each foreign key lookup as a separate query may not be a bad thing.
If the cardinality of the foreign keys doesn't change much (you don't
add frequently add potential new values for that foreign key), then it
is highly likely that those queries would be served from the query
cache.

If you are displaying 1 million different forms, but all their foreign
key lookups can be served from cache, do you think it would be quicker
to do 1 million queries with 5 joins in each query, with none of the
queries cached, or 5 million queries, 1 million with no joins and 4
million served from the query cache?

Cheers

Tom

[Lee Hinde]

Thanks Tom; I may have been unclear. I would expect at least one query per foreign key to load the potential values, but there is a query for each potential value. i.e.., one of the foreign keys is to an instructor table. There's that query, then there's a query for each instructor as part of the class page load.

And yes, I have a lot of nullable foreign keys. In this example, I can have a class but not know yet who the instructor will be. I suppose an alternative to a nullable key would be to have a default, 'no decision made yet' option. Thanks also for the reminder about select_related not following a nullable fk.

[Tom Evans]

When turning an instance of an object into a <option> element, Django
calls the unicode function on that instance. If your __unicode__
method for that object references related objects, then Django will
have to issue a query for each instance.

Eg, if you had a models like this:

class Store(Model):
name = CharField()
region = ForeignKey('Region')

class Region(Model):
name = CharField()
country = ForeignKey('Country')
def __unicode__(self):
return u'%s (%s)' % (self.name, self.country.name)

class Country(Model):
name = CharField()

and you were displaying a form for a Store instance, it would do one
query to look up all the related regions, but when it comes to render
the region options, it will issue an extra query per region in order
to look up the country name.

Remember that select_related() will happily follow nullable foreign
keys, you just have to explicitly ask for it by specifying the
relations you want loaded.

Cheers

Tom