Django定制左外加 [英] Django Custom Left Outer Join

问题描述

我已经使用这个

news = News.objects.filter(Q(likes__user__isnull=True)|Q(likes__user=user))
.extra(select={"is_liked":NewsLikes._meta.db_table+".user_id = %d" % user.id})

这给我以下查询

SELECT (shows_newslikes.user_id = 143) AS `is_liked`, * FROM `shows_news` 
LEFT OUTER JOIN `shows_newslikes` ON ( `shows_news`.`id` = `shows_newslikes`.`news_id`)
WHERE (`shows_newslikes`.`user_id` IS NULL OR `shows_newslikes`.`user_id` = 143 )

想要的是以下查询作为结果

what i want is the following query as an outcome

SELECT (shows_newslikes.user_id = 143) AS `is_liked`, * 
FROM `shows_news` LEFT OUTER JOIN `shows_newslikes` ON ( `shows_news`.`id` = 
`shows_newslikes`.`news_id` and `shows_newslikes`.`user_id` = 143 ) WHERE 
(`shows_newslikes`.`user_id` IS NULL  )

所以我有e做查询Django模型

So what i have to do in query Django model

推荐答案

很难生成这种形式的 LEFT OUTER JOIN 不使用 raw（）;还需要 distinct（）重复的行。我会使用 EXISTS ，它更干净，可能会更快：

It's hard to generate this form of LEFT OUTER JOIN without using raw(); Also you need to distinct() duplicated rows. I would use EXISTS which is cleaner and likely to be faster:

news = News.objects.extra(select={'is_liked':
    'EXISTS (SELECT 1 FROM {tbl_2} '
    'WHERE {tbl_2}.news_id = {tbl}.id AND {tbl_2}.user_id = %s)'.format(
        tbl=News._meta.db_table,
        tbl_2=NewsLikes._meta.dbtable)}, select_params=(user.id,))

这篇关于Django定制左外加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！