I have article and bookmark tables and i want to fetch articles while getting is_bookmarked flag if the user has bookmarked the article.

Article table has all the meta data related to the article and the bookmark table has user and article columns to maintain which user has bookmarked which article i.e Consider Bookmark table with columns article_id and user_id and a combined unique constraint of these.

SELECT a.id AS article_id, a.title, (b.article_id IS NOT NULL) AS is_bookmarked FROM article_article a LEFT OUTER JOIN article_bookmark b ON a.id = b.article_id AND b.user_id = 1

Now i want to replicate this sql query to an django orm query, i don’t want to use CASE or exists, and i’m unable to replicate this query just because of the is_bookmarked flag.

I’ve tried FilteredRelations but couldn’t get the query replicated.