On Mon, Feb 23, 2009 at 12:40 PM, sphogan <spho...@gmail.com> wrote:
>
> I'm surprised that this hadn't come up for me before, but nonetheless,
> here I am.
>
> My problem is that I want to get related items (in a similar situation
> to getting blog articles with their comments) without looping a second
> lookup (since it would be a couple thousand items and building a page
> off a couple thousand queries isn't a good idea).
>
> Basically, the schema is like this:
>
> Article(models.Model):
> #fields for article
>
> Comment(models.Model):
> #fields for comment
> article = models.ForeignKey(Article)
>
> Simple enough. If I wanted to get all the comments and their related
> article, I could do:
>
> Comment.objects.select_related('article').all()
>
> However, select_related() only works on the object the foreign key is
> declared on (http://docs.djangoproject.com/en/dev/ref/models/querysets/
> #id4 <http://docs.djangoproject.com/en/dev/ref/models/querysets/%0A#id4>;
> specifically, "You can only refer to ForeignKey relations in the
> list of fields passed to select_related.") Presumably this is because
> it does an INNER JOIN rather than a LEFT OUTER JOIN and would
> therefore miss articles with no comments.
>
> What I would like to be able to do is:
>
> Article.objects.select_related('comment_set').all()
>
> This seems like a somewhat simple case and there seems to be no
> mention of it in the documentation anywhere.
>
Given an Article instance a, a.comment_set.all() will get you the related
comment in one SQL query. If you want that query to pull in all related
stuff, then use a.comment_set.select_related().all(). You can see the
effect via some simple shell experiments. Below I substitute 'Puzzles' for
'Article' and 'Clues' for 'Comments', as for my DB they have the same
one-many relationship you are describing, and Clues have another related
field that may be of interest when looping over a clues set.
Python 2.5.1 (r251:54863, Jul 31 2008, 23:17:40)
[GCC 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from crossword.models import Puzzles
>>> from django.db import connection
>>> p = Puzzles.objects.get(pk=2834)
>>> p.clues_set.count()
74L
>>> len(connection.queries)
2
>>> for c in p.clues_set.all():
... print c.EntryID,
...
<output deleted>
>>> len(connection.queries)
77
Pulling the clues_set and the EntryID for each clue required 75 queries: 1
for the set itself, 1 for each clue to retrieve the related EntryID value.
However you can cut that down to one query by simply specifying
select_related() as appropriate. Using a different puzzle just to guarantee
no cached results are used:
>>> p = Puzzles.objects.get(pk=8643)
>>> p.clues_set.count()
78L
>>> len(connection.queries)
79
>>> for c in p.clues_set.select_related().all():
... print c.EntryID,
...
<output deleted>
>>> len(connection.queries)
80
>>>
In this case the loop only took one query, which retrieved the set of clues
and all their related objects.
Maybe this helpful for you to reduce the number of queries you see you code
generating? Might not get down to 1 if you are looping over many Articles,
but 1 per Article is better than #Comments referenced by the set of
Articles?
Karen
--~--~---------~--~----~------------~-------~--~----~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
