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+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.