Django Custom Left Outer Join

Question

I have query the Django model with this

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})

which gives me following query

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  )

So what i have to do in query Django model

What exactly are you trying to do? Do you want to annotate each resulting `News` object with a boolean that tells you whether or not that row is 'liked'? — Timmy O'Mahony, Sep 13 '14 at 10:11

score 4 · Accepted Answer · answered Nov 05 '14 at 02:44

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 Custom Left Outer Join

1 Answers1

Linked