Django: Return all Values even if filtering through a related model

Question

Thanks to some fantastic help on a previous question I have managed to put together my query. Everything works swimmingly save one issue.

    careers = Career.objects.filter(job__dwarf__user = 1).annotate(dwarves_in_career = Count('job__dwarf'))

In my view this does exactly what I want and when I loop it in my template like so:

{% for career in careers reversed %}
    <li>{{ career.name }}: {{ career.dwarves_in_career }}</li>
{% endfor %}

I get what I expected. My issue is that the above code will only return the Careers that have dwarves. I need it to return all careers:

Unassigned: 1
Construction: 1
Crafting: 2
Gathering: 0
Farming: 0

is my expected results. Instead the last two rows are not there because I am filtering for the specific user. I know why it's happening, what I am trying to figure out is how to get the query to return all careers, even if their dwarf count for a particular user is 0.

Answer 1

I think you need to swap over your annotate and filter clauses. See the documentation on this - the way you have it, it filters the queryset, but you actually want to keep the entire queryset but just filter the annotations.

Answer 2

So, I eventually figured it out...

from django.db.models import Count, Q

def fortress(request):
    careers = Career.objects.filter(Q(job__dwarf__user = 1) | Q(job__dwarf__user__isnull = True)).annotate(dwarves_in_career = Count('job__dwarf'))
    return render_to_response('ragna_base/fortress.html', {'careers': careers})

Not exactly sure what Q is but it works!

More information here: http://www.djangoproject.com/documentation/models/or_lookups/

Basically what I am doing is finding all careers with dwarves (based on user_id) OR all careers that have no dwarves (based on null)