Efficient reverse ForeignKey/ManyToMany data field access

Hi there,

I am trying to customize the django changelist and I have been having
trouble displaying data across reverse ForeignKey relationships.
Despite searching at length I have been unable to find a better method
to display this data than using _set.all(). My issue with this
solution is that it requires O(n) database queries. Is there any way
to access the data more efficiently?

Example:

Host
name = CharField
Account
name = CharField
host = ForeignKey(Host, related_name='accounts')

What I want to do is list the associated account names for every host
on the changelist. The problem with adding

def get_accounts(self, host):
return host.accounts.all()

to the list_display of the host admin model is that it will then hit
the database O(n) times where n is the number of rows in the list.

My first solution was performing a raw query with ' ...
GROUP_CONCAT(account.id) ... ORDER BY host.id' and then matching the
account id against a cache of account id -> account name. I have since
refined this by creating a custom aggregate to perform the aggregate.
Once the data has been found it, again, uses a cache of account id ->
account name.

So, my questions are as follows:

1. How should I be going about this?
2. Is support for this type of function ever going to be build into
django? (I have noticed that the docs state that doing this from
list_display in admin would require O(n) database queries, and no
mention is made of methods other than _set.all() to retrieve the data,
at least that I was able to find).

Thanks,

Chad

PS: In case anyone else runs into the same problem in the future, here
is the code that I am currently using:

~~~aggregates.py~~~
from django.db.models import Aggregate
from django.db.models.sql.aggregates import Aggregate as SQLAggregate,
AggregateField as SQLAggregateField

string_aggregate_field = SQLAggregateField('TextField')

class GroupConcat(Aggregate):
name = 'GroupConcat'

def add_to_query(self, query, alias, col, source, is_summary):
# Manually override the aggregate class
klass = SQLGroupConcat # getattr(query.aggregates_module,
self.name)
aggregate = klass(col, source=source, is_summary=is_summary,
**self.extra)
query.aggregates[alias] = aggregate

class SQLGroupConcat(SQLAggregate):
sql_function = 'GROUP_CONCAT'

def __init__(self, col, **extra):
super(SQLGroupConcat, self).__init__(col, **extra)
tmp = string_aggregate_field
self.field = tmp

~~~admin.py~~~~
...
from aggregates import GroupConcat
...
class HostAdmin(admin.ModelAdmin):
list_display = ('__unicode__', 'account_names')
def queryset(self, request):
self.account_cache = {}
return super(HostAdmin,
self).queryset(request).annote(GroupConcat('accounts'))
def account_names(self, host):
# Cache the related objects to avoid O(n) queries
if not self.account_cache:
queryset = Account.objects.all()
for obj in queryset:
self.account_cache[obj.id] = obj.name
# Parse the account list and output formatted links
accounts = host.accounts__groupconcat
if not accounts:
return 'none'
accounts = accounts.split(',')
result = ''
num_accounts = len(accounts)
for i in range(0, num_accounts):
account = int(accounts[i])
name = self.account_cache[account]
url =
urlresolvers.reverse('admin:accounts_account_change', args=(account,))
result += '<a href=\'%s\'>%s</a>' % (url, name)
if i < num_accounts:
result += '<br />'
return result
account_names.allow_tags = True

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.