On 1 июл, 01:35, Michal Petrucha <
michal.petru...@ksp.sk> wrote:
> On Thu, Jun 30, 2011 at 02:13:05PM -0700, peroksid wrote:
> > Thank you, Michal,
>
> > Unfortunately, I need join condition, not a WHERE clause, which can be
> > easily extended with extra() method arguments.
> > It is not my bad mood, simply the same condition in WHERE and ON
> > produces different effect.
>
> Hmm, could you please provide an example? (-: Don't know whether it's
> the late night combined with lack of caffeine or something else but I
> can't figure out the difference at the moment...
>
Example, yes. As Haroon said, it is LEFT OUTER JOIN.
We have models reduced for convenience's sake here:
class Campaign(models.Model):
pass
class CampaignExtraStatus(models.Model):
campaign = models.ForeignKey(Campaign)
user = models.ForeignKey(User)
There is campaign with id = 34, user with id - 17, CampaignExtraStatus
for user_id = 17 does not exist:
select count(*) from campaign_campaign where campaign_campaign.id = 34
count
=====
1
select count(*) from auth_user where auth_user.id = 17
count
=====
1
select count(*) from campaign_campaignextrastatus where user_id = 17
and campaign_id = 34;
count
=====
0
Now, query of type "a" takes all campaigns and fills columns from
NULLs where no row in campaign_campaignextrastatus for join condition.
Besides user_id there are useful data in that table.
a) SELECT "campaign_campaign"."id",
"campaign_campaignextrastatus"."user_id" FROM "campaign_campaign" LEFT
OUTER JOIN "campaign_campaignextrastatus" ON ("campaign_campaign"."id"
= "campaign_campaignextrastatus"."campaign_id" AND
campaign_campaignextrastatus.user_id = 17) WHERE
"campaign_campaign"."id" = 34
id | user_id
============
34 | Null
============
1 row
b) SELECT "campaign_campaign"."id",
"campaign_campaignextrastatus"."user_id" FROM "campaign_campaign" LEFT
OUTER JOIN "campaign_campaignextrastatus" ON ("campaign_campaign"."id"
= "campaign_campaignextrastatus"."campaign_id") WHERE
("campaign_campaign"."id" = 34 AND
campaign_campaignextrastatus.user_id = 17)
id | user_id
============
============
0 rows
c) SELECT "campaign_campaign"."id",
"campaign_campaignextrastatus"."user_id" FROM "campaign_campaign" LEFT
OUTER JOIN "campaign_campaignextrastatus" ON ("campaign_campaign"."id"
= "campaign_campaignextrastatus"."campaign_id") WHERE
("campaign_campaign"."id" = 34 AND
(campaign_campaignextrastatus.user_id = 17 OR
campaign_campaignextrastatus.user_id IS NULL ))
id | user_id
============
============
0 rows
Queries "b" and "c" have the condition in WHERE, where it can not
produce required impact. It works in join condition clause, and only
there.
--
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+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
