Left Outer Join
Chris
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.
Regards,
Chris
Kenneth Gonsalves
On 13-Jul-08, at 11:07 AM, Chris wrote:
> 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.
use your own sql statement
--
regards
kg
http://lawgon.livejournal.com
http://nrcfosshelpline.in/code/
Russell Keith-Magee
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.
Option 3 - Look into select_related(). The purpose of select_related
is to populate related objects when retrieving a base object. It
sounds like this might be what you are trying to do.
Yours,
Russ Magee %-)
Malcolm Tredinnick
On Sun, 2008-07-13 at 20:38 +0800, Russell Keith-Magee wrote:
> On Sun, Jul 13, 2008 at 1:37 PM, Chris <chri...@gmail.com> 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