Re: Weird join conditions

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.