How to calculate a annotated proportion on a filter query in Django?

Question

For a poll app, I want the product_code, Count() and proprtion of product ratings > 3 for each product rating ("Product_r") object (grouped on product_code).

I was hoping I could get away with a calculation in the last annotation expression, but it seems not.

q = Product_r.objects.all()\
    .annotate(product_code=F('fk_product__product_code'))\
    .values('product_code')\
    .annotate(count=Count('id'))\
    .annotate(proportion=Count(score__gt=3)/count)

The below attempt doesn't work either (complains that count is not defined, and also it filters the whole query, not just the subsequent annotate part that calculates the proportion):

q = Product_r.objects.all()\
    .annotate(product_code=F('fk_product__product_code'))\
    .values('product_code')\
    .annotate(count=Count('id'))\
    .filter(score__gt=3)\
    .annotate(proportion=Count('id')/count)

Is there a way to do this without coding the query twice (one of them filtering on score__gt=3), and then dividing the two count values? This blog post uses raw SQL for this sort of thing - I hope it's possible to avoid that in my case here.

Try `Count('id')/F('count')`. I'm not sure whether it will work, but it will stop the error about count not being defined. — Alasdair, Apr 14 '16 at 21:10
You're right thanks, it did stop the error. The query still doesn't produce the correct output though - the filter applies to the whole query, making all proportions equal `Count(everything)/Count(everything)` i.e. 1. — Escher, Apr 14 '16 at 21:21

score 0 · Accepted Answer · answered Apr 20 '16 at 06:06

Well, to get this into production here's a simplified version of the solution I used. As the tables get bigger I'm going to probably reduce the scope of the queries so that it won't be possible to fetch more than N records. Hope it's useful to someone.

#http://stackoverflow.com/a/3422287/3790954
#INNER JOIN two lists of dictionaries based on a common dictionary key
def merge_lists(l1, l2, key):
    merged = {} #will be a dict of dicts, with the primary key being the value to join on
    for item in l1+l2:
        if item[key] in merged:
            merged[item[key]].update(item)  
        else:
            merged[item[key]] = item
    return [v for (k, v) in merged.items()]

@login_required
def tabulate_results(request):
    first_list = Model1.objects.all().values_list('id', 'something', 'something_else')
    second_list = Model2.objects.all().values_list('id', 'other_stuff')
    #perform some sort of list comprehension here to get l1 and l2 
    #with the values you're interested in, then merge.
    merged_list = merge_lists(l1, l2, 'id')
    return render(request, "template.html", {"my_data": merged_list}

Then tabulate in the template.html and sort with javascript (pushing some of the computation work onto the client).

How to calculate a annotated proportion on a filter query in Django?

1 Answers1