The problem with this is, when the foreignkey can be null the only way
to reference it properly is with a LEFT JOIN. Now, I'm not sure why it
isnt implemented in ORM, but I strongly encourage you to find a
differently solution, as LEFT JOINs can be VERY costly on system
resources.
On the same note, I do believe the ORM could handle not processing
duplicate queries in the same scope a little bit better. If I'm saying
SomeModel.objects.filter(foreignkey=my_instance) there should be no
reason that we can't set foreignkey to my_instance so it wouldn't have
to be queried for if we wanted to do select_related or just reference
it.
On Mar 5, 1:43 pm, "Ilya Semenov" <[EMAIL PROTECTED]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
