select_related() - what is wrong with my design

Using Django Using the ORM
1

My model:

class Foo(models.Model):
    name = models.CharField(unique=True, max_length=50)
    self_foo = models.ForeignKey(to="self", on_delete=models.PROTECT)

class Bar(models.Model):
    name = models.CharField(unique=True, max_length=30 )
    parent_foo = models.ForeignKey(to=Foo, on_delete=models.PROTECT)

Lets do some inspections:

>>> from polls.models import Foo, Bar
>>> print(Bar.objects.all().query)
SELECT "polls_bar"."id", "polls_bar"."name", "polls_bar"."parent_foo_id" FROM "polls_bar"

This looks as expected.
However:

>>> print(Bar.objects.select_related().query) 
SELECT "polls_bar"."id", "polls_bar"."name", "polls_bar"."parent_foo_id", "polls_foo"."id", "polls_foo"."name", "polls_foo"."self_foo_id", T3."id", T3."name", T3."self_foo_id", T4."id", T4."name", T4."self_foo_id", T5."id", T5."name", T5."self_foo_id", T6."id", T6."name", T6."self_foo_id" FROM "polls_bar"
INNER JOIN "polls_foo" ON ("polls_bar"."parent_foo_id" = "polls_foo"."id")
INNER JOIN "polls_foo" T3 ON ("polls_foo"."self_foo_id" = T3."id")
INNER JOIN "polls_foo" T4 ON (T3."self_foo_id" = T4."id")
INNER JOIN "polls_foo" T5 ON (T4."self_foo_id" = T5."id")
INNER JOIN "polls_foo" T6 ON (T5."self_foo_id" = T6."id")

Why there is four extra tables joined in my query? How can I control that? I’d like only one extra join.
I’ve found the magic setting max_depth=5 in django.db.models.sql.query, but this seems very internal. I now that I can do Bar.objects.select_related("parent_foo") to stop this behaviuor, however then I have to specify all other foreing keys I want to include as related in my query.

In reality my model is much bigger, and I want to use build in ModelAdmin, and I really don’t want to go through maintaing select_related setting in all of my models just to list all FK’s there. Without this, I am very quickly hitting limits such as jango.db.utils.OperationalError: at most 64 tables in a join or too many columns in result set.

2

The issue is the foreign key to self in Foo.

You can prevent the recursion by specifying the references in the select_related clause.

e.g., Bar.objects.select_related('parent_foo').all()

Be aware that you never need to use select_related. It’s a performance enhancement, not a requirement. You can selectively use it in each query as needed. If you’ve got a view that only uses 4 of the foreign keys in the query results, then you can, if desired, only list those 4. (Or not, your choice.)

Also, if the purpose of the ForeignKey to self in Foo is to create a tree structure of indeterminate depth, then this is not how you want to do it in a relational database. (If the intent of it is only to allow for a single level, then it’s ok.)

3

This is what I’d rather avoid. As I said, I am using ModelAdmin and don’t want to customize it unless absolutely necessary.

There is only one level of self in Foo.

4

If this is something you’re using in the Admin, then I wouldn’t worry about the select_related at all.

Or use it selectively, your choice. However, because it is completely optional, you are never required to keep adding entries as your models change.

5

I have extra attributes in Foo and in Bar, and I am hitting the limits for the number of columns and joins very quickly.

6

Then don’t use select_related. Yes, it causes Django to issue additional queries to retrieve that data, but since this is the admin, it’s not like there are dozens of people using it, so it shouldn’t be a real problem.