Django の annotate の集計で group by でハマったのでメモ
結果的に正解はこんな感じ
from django.db.models import Count
hoges = Hoge.objects.filter(fuga__regex='/mogera/[0-9]+/$').values('fuga').annotate(num_fuga=Count('fuga')).order_by('-num_fuga')
[hoge['num_fuga'] for hoge in hoges]
values 挟まない失敗パターン
.values() 挟まないと id で group by されちゃう
print(Hoge.objects.filter(fuga__regex='/mogera/[0-9]+/$').values('fuga').annotate(num_fuga=Count('fuga')).order_by('-num_fuga').query)
# -> SELECT `hoge`.`fuga`, COUNT(`hoge`.`fuga`) AS `num_fuga` FROM `hoge` WHERE `hoge`.`fuga` REGEXP BINARY /mogera/[0-9]+/$ GROUP BY `id`.`fuga` ORDER BY `num_fuga` DESC
