I am trying to perform a filter, count, and sort on a Django 1.6 dataset

Question

I am making a little word matching game with django where I start the user with a word (model: refword) and they type in what they think the next word (model: word) should be.

The word they type in then becomes the next refword awaiting for a word.

Ex:

1. Home page displays "When I say Green, you say _."
2. User types and submits "Tree"
3. Then the next page displays "When I say Tree, you say __."

Problem: After the user submits "Tree" on the second step and goes to step 3, I would like to show what other users typed in and the counts of those word pairs.

See Desired Result section of my question below. I have been having a hard time getting the queries in my view to get me close to what I want.

So continuing with this example, let's say the user gets "When I say Green, you say _" and types in "Tree."

This writes refword: Green and word: Tree to the database.

Sample Database Data

refword word
------- -------
green   grass
green   grass
blue    sky
blue    diamond
green   clover
green   tree
green   grass
blue    sky
red     sky
green   party
red     head
green   day
green   tree <--- what the user just typed in

Desired Display Result

Here's what has been said so far:

refword word    qty
------- ------- ---
green   grass   3
green   tree    2
green   clover  1
green   day     1
green   party   1

Here's my understanding of what would need to happen to create this.

Filtering: Show distinct results for the refword "green"

Calculations: Calculate and display the sum of times each result appears.

Sorting: Sort by quantity descending so highest qty is at the top. Sub-sort alphabetically ascending (A to Z) (ex: green clover, green day, and green party all have 1 one result, so sort the alpha ascending sort puts clover at the top.)

Here are some code snips of

models.py

class Word(models.Model):
word = models.CharField(max_length=200)
refword = models.CharField(max_length=200)

form.py

class WordForm(forms.Form):
word = forms.CharField(max_length=30)

views.py

I'm setting up my view like this

def submitword(request):
if request.method == 'POST': # If the form has been submitted...
    form = WordForm(request.POST) # A form bound to the POST data
    if form.is_valid(): # All validation rules pass
        word = form.cleaned_data['word']        
        referring_word = request.session["referring_word"] #setup the session
        word_form_data = Word(word=word, refword=referring_word)

Template file

<p>When I said {{ ref_word }} you said {% form.word.value %}</p>
<p>Here's what has been said so far:</p>

Answer 1

There isn't a online query that you can use to get required info. Suboptimal way would be to use distinct('word') and then get count for each work. But it will only work with pgsql.

qs = Word.objects.filter(refword=referringword).distinct('word')
cnt_list = dict()
for w in qs:
    cnt_list.update({w.word: Word.objects.filter(refword=w.refword, word=w.word).count()})

---

Other way without using distinct() and 2nd set of queries is...

Use values() to get dict of required objects and use regroup template filter tag to show the words and their count. But in this case sorting cannot be done on word count.

#views
word_list = Word.objects.filter(refword=referringword).values('refword', 'word')
#pass this to template

#template
{% regroup word_list|dictsort:"word" by word as word_count %}
<ul>
    {%for wl in word_count %}
       <li> {{wl.grouper}} : {{wl.list|length}} </li>
    {%endfor%}
</ul>