additional conditions on join in django [duplicate]

Question

Is it possible to add an additional condition to join statement created by django ORM?

What I need in SQL is

'SELECT "post"."id", COUNT("watchlist"."id") FROM "post"   LEFT OUTER JOIN "watchlist"      ON ("post"."id" = "watchlist"."post_id" AND "watchlist"."user_id" = 1)   WHERE "post"."id" = 123  GROUP BY … 

In django most of this is

Post.objects.annotate(Count('watchinglist')).get(pk=123) 

But how can I add AND "watchlist"."user_id" = … into JOIN condition with django ORM?

Adding it to filter fails to get Post objects with no associated objects in watchlist.

Answer 1

In Django v2.0 use FilteredRelation

Post.objects.annotate(     t=FilteredRelation(         'watchlist', condition=Q(watchlist__user_id=1) ).filter(t__field__in=...)