Django conditional annotation count with filter (many-to-many) follower/following table
<p>This is a bit of a mind bender for me so I decided to ask for some help.</p>

<p>What I have is a standard user model and a table which tracks if users follow each other which has a <code>from_user</code>, <code>to_user</code>, and <code>following</code> values. The first to being foreignkeys to my user table the third is a boolean value.</p>

<p>What I want to do is be able to override the default <code>get_queryset</code> method in django admin so I can sort based on the number of followers the users have.</p>

<p>Where I'm currently stuck is at actually annotating the number of followers, it might be something small I'm missing and derping out but here is my code:</p>

<pre><code>def get_queryset(self, request):
    return AuthUser.objects.annotate(
        followers_count=Sum(
            Case(
                When(user_relations__from_user=1, user_relations__to_relation__following=True, then=1),
                default=0,
                output_field=IntegerField()
            )
        ))
</code></pre>

<p>The part that is wrong - I'm stuck at using the <code>user_relations__from_user=1</code> which should be the current user being evaluated's id.</p>

<p>What I need to do in the aggregation for all entries is something I'm already doing on a single case scenario inside the User model:</p>

<pre><code>@property
def num_followers(self):
    return UserRelations.objects.filter(from_user=self, following=True).count()
</code></pre>

<p>It seems simple but how do I get the <code>from_user=self</code> when annotating?</p>

<p>EDIT: Adding stack trace when trying to use F <code>user_relations__from_user_id__pk=F('id')</code></p>

<p>An additional note - my <code>from_user</code> and <code>to_user</code> fk fields are actually named <code>from_user_id</code> and <code>to_user_id</code> - hope this doesn't lead to additional confusion. Sorry about that.</p>

<pre><code>Internal Server Error: /admin/pkm_user/authuser/
Traceback (most recent call last):
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/core/handlers/base.py", line 132, in get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/contrib/admin/options.py", line 616, in wrapper
    return self.admin_site.admin_view(view)(*args, **kwargs)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/utils/decorators.py", line 110, in _wrapped_view
    response = view_func(request, *args, **kwargs)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/views/decorators/cache.py", line 57, in _wrapped_view_func
    response = view_func(request, *args, **kwargs)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/contrib/admin/sites.py", line 233, in inner
    return view(request, *args, **kwargs)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/utils/decorators.py", line 34, in _wrapper
    return bound_func(*args, **kwargs)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/utils/decorators.py", line 110, in _wrapped_view
    response = view_func(request, *args, **kwargs)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/utils/decorators.py", line 30, in bound_func
    return func.__get__(self, type(self))(*args2, **kwargs2)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/contrib/admin/options.py", line 1548, in changelist_view
    self.list_max_show_all, self.list_editable, self)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/contrib/admin/views/main.py", line 47, in __init__
    self.root_queryset = model_admin.get_queryset(request)
  File "/home/bastor/Work/pokemall-api/django/pkm_user/admin.py", line 65, in get_queryset
    output_field=IntegerField()
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/db/models/query.py", line 794, in annotate
    obj.query.add_annotation(annotation, alias, is_summary=False)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/db/models/sql/query.py", line 977, in add_annotation
    summarize=is_summary)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/db/models/aggregates.py", line 20, in resolve_expression
    c = super(Aggregate, self).resolve_expression(query, allow_joins, reuse, summarize)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/db/models/expressions.py", line 491, in resolve_expression
    c.source_expressions[pos] = arg.resolve_expression(query, allow_joins, reuse, summarize, for_save)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/db/models/expressions.py", line 779, in resolve_expression
    c.cases[pos] = case.resolve_expression(query, allow_joins, reuse, summarize, for_save)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/db/models/expressions.py", line 713, in resolve_expression
    c.condition = c.condition.resolve_expression(query, allow_joins, reuse, summarize, False)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/db/models/query_utils.py", line 91, in resolve_expression
    clause, joins = query._add_q(self, reuse, allow_joins=allow_joins, split_subq=False)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/db/models/sql/query.py", line 1332, in _add_q
    allow_joins=allow_joins, split_subq=split_subq,
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/db/models/sql/query.py", line 1194, in build_filter
    lookups, value)
  File "/home/bastor/Work/pokemall-api/env/local/lib/python2.7/site-packages/django/db/models/fields/related.py", line 1709, in get_lookup_constraint
    raise exceptions.FieldError('Relation fields do not support nested lookups')
FieldError: Relation fields do not support nested lookups
</code></pre>