additional conditions on join in django

user_62821 0
Viewed 10995

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.

django python
3 Answers

In Django v2.0 use FilteredRelation

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

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)
user_65464450
Related