Additional conditions for joining the django

Question

Can I add an additional condition for the join operator generated by django ORM?

What do I need in SQL,

'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

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

But how can I add AND "watchlist"."user_id" = … to the JOIN condition using django ORM?

Adding it to the filter does not allow you to get Post objects without related objects in the watchlist.

Answer 1

I found a similar question here: Django - filtering related objects

And there seems to be no way to do this without special SQL.

Answer 2

Short answer: under certain conditions, yes.

When building LEFT JOINs with a GenericForeignKey, Django calls GenericRelation.get_extra_restriction , which adds an additional condition to the ON clause with the restriction content_type_id.

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

You can use this place to add additional restrictions to the ON clause if you manage to organize your code to get the corresponding restriction parameters at a specific 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) 

Answer 3

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

Happy coding.

Answer 4

In Django v2.0 use FilteredRelation

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