list_editable duplicate queries

[chadc]

Hi there,

I think that I have found a bug with the rendering of list_editable
objects in the change list. The problem that I am running into is that
if I include a field that is a foreign key in list_editable, the
change_list hits the database every time that it renders the foreign
key's widget. This means that it hits the database O(m*n) times where
m is the number of editable foreign keys in the admin model and n is
the number of rows being rendered in the change_list.

For example, say that we have the following models:

class Host(models.Model):
name = models.CharField(max_length=128, unique=True)

class Account(models.Model):
host = models.ForeignKey(Host, related_name="accounts")
name = models.CharField(max_length=128)

...and the following admin model:

class AccountAdmin(admin.ModelAdmin):
list_display = ('name', 'host')
list_editable = ('host',)

Then if we load up a bunch of data (one host, 40 accounts) and go to
the change_list (/admin/accounts/account), we will find that the
database is hit (m*n)+3 = 43 times. The queries that it executes are
as follows:

EXECUTIONS | TIME | QUERY

40 | 384 ms | SELECT "hosts_host"."id", "hosts_host"."name" FROM
"hosts_host"

1 | 17 ms | SELECT "auth_message"."id", "auth_message"."user_id",
"auth_message"."message" FROM "auth_message" WHERE
"auth_message"."user_id" = %s

1 | 0 ms | SELECT COUNT(*) FROM "accounts_account"

1 | 0 ms | SELECT "accounts_account"."id",
"accounts_account"."host_id", "accounts_account"."name",
"hosts_host"."id", "hosts_host"."name" FROM "accounts_account" INNER
JOIN "hosts_host" ON ("accounts_account"."host_id" =
"hosts_host"."id") ORDER BY "accounts_account"."id" DESC

As you can see, the vast majority of the hits (40/43) were for the
exact same query and served only to get a list of all of the hosts. I
think that this occurs when the foreignkey Select widget's options are
rendered at django/forms/widgets.py:~411.

I realize that I am probably doing something wrong, so any advice
about the correct way of doing this would be appreciated. If this is a
legitimate bug, is there anything that I can do to avoid it but still
get the same functionality?

Thanks!


PS: Please let me know if you need any more information (screen shots,
etc).

[chadc]

As a follow-up to my last message, there seems to be another issue
when the foreign key is optional. If you set 'null=True, blank=True'
on the host field of the accounts model above, Django joins everything
except the foreign key and then hits the database every time it needs
data relating to the foreign key. The result is another O(m*n)
database hits where m is the number of foreign keys displayed by the

admin model and n is the number of rows being rendered in the

change_list. These hits would be unnecessary if Django were to use a
LEFT OUTER JOIN when rendering potentially null foreign keys. To
continue with the example above, the queries are now:

EXECUTIONS | TIME | QUERY

40 | 396 ms | SELECT "app_host"."id", "app_host"."name" FROM
"app_host"

40 | 331 ms | SELECT "app_host"."id", "app_host"."name" FROM
"app_host" WHERE "app_host"."id" = %s
----> would not be necessary if the main query
(below) used a LEFT OUTER JOIN

1 | 15 ms | SELECT COUNT(*) FROM "app_account"

1 | 10 ms | SELECT "auth_message"."id", "auth_message"."user_id",

"auth_message"."message" FROM "auth_message" WHERE
"auth_message"."user_id" = %s

1 | 0 ms | SELECT "app_account"."id", "app_account"."host_id",
"app_account"."name" FROM "app_account" ORDER BY "app_account"."id"
DESC
----- >should include a LEFT OUTER JOIN on app_host
where app_host.id == app_account.host_id

Again I realize that this might be my mistake, so any input would be
appreciated.



Also, as a follow up to my last email, I think that that is indeed a
bug in Django. Not only does the documentation list a similar issue
for using list_display on ManyToMany fields, but I have also managed
to create two fixes (hacks?) that address the problem:

1. Adding a name-based cache to the Select widget rendering function
(django\forms\widgets.py:~431). This fix works, but it is clearly less
than ideal for its lack of flexibility and the fact that it does not
address the underlying issue.

2. Manually rendering the data like a widget in admin.py using a
custom display function that caches the data while rendering the first
row. This also works but again fails to address the underlying issue.

Given that these two fixes work, I am fairly confident that a better
solution is out there. Maybe someone who knows a little more about how
Django decides how to build and cache database queries could help me
figure this out?

Thanks again,

Chad