On Tue, 2009-02-24 at 11:34 -0500, Sean Patrick Hogan wrote: > Thanks for the input! So, I'm assuming by "easiest case" you mean > just calling LEFT OUTER JOINs. That's Rails behavior and then it > deals with it in Ruby. I'm not too familiar with SQLAlchemy, but it > looks like that's what they're doing in their EagerLoader as well. > > The only way I can think of overcoming this would be to do an SQL > UNION with the LEFT OUTER JOIN in the union. That way you're doing > one join per UNION clause and should end up with X + Y rows (which is > better in cases of many multiple related objects, but in the case that > there is 1 or none, would actually add more to the return set). It's > ugly because you have to specify null fillers for the things that > won't exist. As I realise how silly it was to do an OUTER JOIN there, > I retract and change it to INNER JOINs within the UNIONs (since we > don't need to get duplicate rows for relations that don't exist).
Yes, that's the idea. > > That would seem to allow for decent iteration over something to > propagate the appropriate *_set list attributes. > > To illustrate using the blog example (extended with an authors m2m > relationship so that an article can have many authors): > > SELECT * FROM articles /*fill nulls for other columns, cut for > readability*/ > UNION > SELECT * FROM articles /*could also possibly just fill nulls for > articles to make the iteration easier*/ > INNER JOIN comments ON comments.article_id=articles.id > UNION > SELECT * FROM articles > INNER JOIN authors_m2m_table ON > authors_m2m_table.article_id=articles.id > INNER JOIN authors ON authors_m2m_table.author_id=authors.id This is almost correct, except that you will have different numbers of fields in each union sub-query (or possibly the same number). So, in order to be able to differentiate which rows are for which results, you'd also select a constant value (1, 2, 3, ...) to indicate which sub-query it's coming from. > > > The problem with that is that any filters would have to be repeated > multiple times - like if you just wanted articles written by Malcom, > you'd have to add the explicit joins on each clause to the union. Yes, they're effectively all separate queries that have the results jammed together. It's still slightly (but only slightly, in most cases) more efficient than doing the same thing in Python. Whether all the added complexity is worth it, though, or whether we just restrict it to one field and say "do the rest in Python" remains to be seen from how complicated the patch ends up looking. You've certainly understand the details of the solution. You can see that it gets a little complex. Not technically difficult, just fiddly to implement. Which is kind of why it hasn't been done yet (at least by me). The payback on effort required is lower than for a bunch of other things that are biting people. Regards, Malcolm --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
