additional conditions on join in django [duplicate]

This question already has answers here: Django - filtering on related objects (5 answers) Closed 3 years ago.

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.开发者_如何学Goannotate(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.

---

In Django v2.0 use FilteredRelation

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

---

Short answer: in certain conditions - yes.

When construction LEFT JOINs with GenericForeignKey, Django calls GenericRelation.get_extra_restriction which adds extra condition to ON clause with "content_type_id" restriction.

For "ForeignKey" this method is also called by returns None.

You may use this place to put extra restrictions into ON clause, if you manage to organize your code to get proper restriction params at certain time.

class UserForeignKey(models.ForeignKey):

    def get_extra_restriction(self, where_class, alias, related_alias):
        field = self.model._meta.get_field('user')
        cond = where_class()
        # Here is a hack to get custom condition parameters
        value = SomeContextManager.get_needed_value()

        lookup = field.get_lookup('exact')(field.get_col(related_alias), value)
        cond.add(lookup, 'AND')
        return cond

class WatchList(models.Model):

    user = UserForeignKey(User)

---

Post.objects.annotate(Count('watchinglist')).filter(pk=123).extra(where=['"watchlist"."user_id" = 1'])

Happy Coding.