On Sun, 2008-07-13 at 20:38 +0800, Russell Keith-Magee wrote: > On Sun, Jul 13, 2008 at 1:37 PM, Chris <[EMAIL PROTECTED]> wrote: > > > > I'm reasonably fluent in SQL, and while I've found Django's ORM is > > great for basic CRUD operations, I'm having trouble creating joins. > > > > For example, I have a three tables, Article, Rating, and User. Rating > > stores a user's rating of a particular article. I'm trying to create a > > view that lists articles, and optionally shows the current user's > > rating of each article, if they've made one. To query the rating > > record along with the article in SQL, this would just be a simple left > > outer join. How would this be done with Django's ORM? I've reviewed > > http://www.djangoproject.com/documentation/model-api/#relationships > > but I don't think it covers this case. > > Option 1 - use raw SQL. This is always an option - Django provides a > nice ORM on top of basic SQL, but there are some queries that are > easier to express in raw SQL than it is to mangle into the ORM. > > Option 2 - The Django ORM automatically creates joins as they are > required by the query that is posed. For the most part, this means > inner joins, but the trunk version of Django does provide some ability > to customize the type of join that is used. However, this isn't > currently documented; it would mean a bit of digging into the > internals.
To extend what Russell's saying a little: by and large, you don't need to specify the type of join. If the relation is non-nullable, an inner join is used. If null results (or non-existent) results are possible in the join (mostly because it's a nullable relation and you're checking for a null value or something like that), a left outer join is used. So Django picks the more-or-less appropriate join type for the relation. The only case where this isn't perfect is where you somehow have external knowledge that the result set won't contain non-existent rows in the join but the relation description is nullable. Django cannot prove (to itself) that no null rows will be generated, so it has to use a left-outer join to avoid missing results. If you were writing the query by hand, you might be able to use an inner join there, but only because of knowledge you have that the database server and Django don't. If you're in that sort of situation and using Django and it's going to make a real difference, that's when you would write custom SQL. Mostly, though, this won't be required (such situations are pretty rare). So don't overthink (or prematurely optimise this). Write the natural queryset filters and you'll find the appropriate join type is used automatically. 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 [email protected] 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 -~----------~----~----~----~------~----~------~--~---
