Hello everyone,
I've run into the problem which I believe is a common use-case and is
already solved, but I can't seem to find the answer neither in
documentation, nor in django-users or django-developers.
Here it goes: how do I optimize a list query if I have null=True
foreign key in my entity? select_related(), as cleary stated in
documentation and as I can see in action, doesn't work in this case.
However I think this is a *very* common use-case and there definitely
should be a solution.
Consider the following model (simplified example):
from django.db import models
from django.contrib.auth.models import User
class Ticket(models.Model):
is_resolved = models.BooleanField()
resolved_by = models.ForeignKey(User, null=True) # None for
unresolved tickets, valid user reference for resolved
...a view which is showing all resolved tickets:
def resolved_tickets(request):
tickets = Ticket.objects.filter(is_resolved=True).select_related()
return HttpResponse(......)
...and a template which is doing something like:
{% for t in tickets %}
{{ t }} resolved by {{ t.user }}
{% endfor %}
If there's a long list of tickets, I'm getting tons of SQL queries
like this:
SELECT
`auth_user`.`id`,`auth_user`.`username`,`auth_user`.`first_name`,`auth_user`.`last_name`,`auth_user`.`email`,`auth_user`.`password`,`auth_user`.`is_staff`,`auth_user`.`is_active`,`auth_user`.`is_superuser`,`auth_user`.`last_login`,`auth_user`.`date_joined`
FROM `auth_user` WHERE (`auth_user`.`id` = 1)
SELECT
`auth_user`.`id`,`auth_user`.`username`,`auth_user`.`first_name`,`auth_user`.`last_name`,`auth_user`.`email`,`auth_user`.`password`,`auth_user`.`is_staff`,`auth_user`.`is_active`,`auth_user`.`is_superuser`,`auth_user`.`last_login`,`auth_user`.`date_joined`
FROM `auth_user` WHERE (`auth_user`.`id` = 2)
SELECT
`auth_user`.`id`,`auth_user`.`username`,`auth_user`.`first_name`,`auth_user`.`last_name`,`auth_user`.`email`,`auth_user`.`password`,`auth_user`.`is_staff`,`auth_user`.`is_active`,`auth_user`.`is_superuser`,`auth_user`.`last_login`,`auth_user`.`date_joined`
FROM `auth_user` WHERE (`auth_user`.`id` = 1)
....and so on.
Questions:
1. From SQL point of view, the whole bunch of queries can be replaced
with a single simple LEFT JOIN query. Is there any way to do that in
Django ORM?
2. In the output above, I can see that even queries for the same
user_id are not cached. I do understand that under the hood, the cache
pool is manager-scope, so accessing FKs of different objects leads to
multiple DB lookups. However, as an end-user, I would like to reduce
the database load and get rid at least of duplicate queries. Are the
any means to achieve that?
Comments/suggestions are welcomed!
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
