models.ForeignKey caused too many SQL queries

Using Django
1

A admin.StackedInline class uses a Model class, and the Model class has a foreign key (models.ForeignKey). This foreign key is generating too many SQL queries, as it tries to make a select list containing all items in the referred-to table (ProductTemplate).

Is there a way to restrict the foreign key to only list one item, instead of all items in the referred-to table?

Any other suggestions will also be highly appreciated. Let me know if you need more details.

Partial source code:

In file admin.py:
...
class TransactionAttributeInlineAttribute(admin.StackedInline):
    model = models.TransactionAttribute
    verbose_name = 'Transaction Link'
    verbose_name_plural = 'Product Links'
class MyAttributeAdmin(admin.ModelAdmin):
    inlines = [TransactionAttributeInlineAttribute]
    list_display = ['name','datatype','required']
...


In file models.py:
...
class TransactionAttribute(models.Model):
    product_template = models.ForeignKey(ProductTemplate)
...
2

Hi @simpleopen, welcome to the forum

Are you not looking for the max_num attribute on an inline? https://docs.djangoproject.com/en/3.0/ref/contrib/admin/#django.contrib.admin.InlineModelAdmin.max_num

3

Hi @adamchainz , thank you for your reply.

I tested max_num like below, see some change but not yet the one I want. It limits the number of items for “TransactionAttribute”, but still a big list generated by foreign key.

Or, should I try something like filtering the results returned by foreign key?

Thank you for your help.

Source code change:

class TransactionAttributeInlineAttribute(admin.StackedInline):
    model = models.TransactionAttribute
    max_num = 1
    verbose_name = 'Transaction Link'
    verbose_name_plural = 'Product Links'
4

Hi, I’m afraid I don’t follow the question at this point.

5

Is there a way to restrict the foreign key to only list one item, instead of all items in the referred-to table?

I’m guessing this is an issue with a ModelChoiceField generating the choices for the form widget.

If so, ModelAdmin.raw_id_fields is a possibility.

HTH

1 Like
6

Or ModelAdmin.autocomplete_fields would be even more user friendly. It gives you a nice select2 based autocomplete widget.

1 Like
7

Thank you, @carltongibson . Yes, your advice works.

It’s a system performance issue, and the culprit was Django’s default behavior in handling database foreign key.

In my case, table of attribute (TransactionAttribute) has a foreign key linking to the one product (ProductTemplate) using this attribute. By default Django makes a drop-down select enumerating all products, not only the linked one but also others unrelated.

We noticed the issue on attribute First Name as it is used by roughly all 100 products, which means to list 100 product links and each link has a drop-down with 100 selections. The overall complexity is on the level of square (100 x 100), and too many SQL queries overwhelmed database.

Another attribute Last Name was also broken for the same reason, while the less-used attributes are still OK.

I fix the issue with your suggestion: get rid of drop-down select, each product link shows only the one linked product, not the others.

Also many thanks to the other replies.