I have a table, Photos, which contains pretty much what you'd expect. A
second table, Albums, contains a field named "key", a title, and other
information. The two are related by a ManyToManyField through
AlbumMembers, which relates the photo ID to the album key ("album"). I
mention AlbumMembers for completeness; it doesn't enter into this
question.
Finally, I have a very sparse table, AlbumOrder, which contains a photo
(ID) and a numeric "order" field. That table has only 44 rows total, 22
for the album I'm testing with.
When I need to collect a list of photos and their order fields (when
specified) I first select the photos from an album into a query set
named "photos" and then use code somewhat like this (I've removed some
error checking that's not relevant here):
for photo in photos.filter(albumorder__album = album.key):
photo.ordering = photo.albumorder_set.filter(album = album.key)
I've timed this code, and for a large album with 65K photos it takes
several seconds to complete. I presume that the problem is that each
filtration inside the loop needs a separate interaction with the SQL
server.
On the other hand, I can pick up the same information in SQL with a join:
SELECT photos.id, `order`, album FROM photos LEFT JOIN album_order ON
album_order.photo = photos.id WHERE album = '00000000' OR album IS NULL;
That takes only 0.27 seconds even including the time needed to output
65K lines to the screen.
So is there a way to get Django to issue this join and collect the
results into an aggregate set? I've read through the docs and
experimented with different filter and double-underscore notations,
without success. It seems silly to do the work with a loop when a
correctly written join can do it so much faster. (And I'll note that if
AlbumOrder had more entries, the loop would take far longer.)
(I've thought about issuing the join directly from my code and reading
from a database cursor, but that seems un-Djangoish.)
--
Geoff Kuenning ge...@cs.hmc.edu http://www.cs.hmc.edu/~geoff/
Software, like bridges, should be elegant and visually pleasing as
well as functional. Ugly constructs, designs, and languages should be
avoided like the plague.
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/pni37k44qcg.fsf%40bow.cs.hmc.edu.
For more options, visit https://groups.google.com/d/optout.
