Randomize a Django queryset once, then iterate through it

Question

I'm writing an app that allows people to compare different pairs of hashtags.

Model:

class Competitors(models.Model):
    tag1 = models.ForeignKey('Hashtag', related_name='+')
    tag2 = models.ForeignKey('Hashtag', related_name='+')
    votes = models.PositiveIntegerField(default=0, null=False)

View:

def compare_hashes(request, i=None):
    i = i or 0
    try:
        competitors = Competitors.objects.order_by('?')[i]
    except IndexError:
        return render(request, 'hash_to_hash.html',
            {'tag1': '', 'tag2': '', i: 0, 'done': True})

    if request.method == 'POST':
        form = CompetitorForm(request.POST)
        if form.is_valid():
            if "yes" in request.POST:
                competitors.votes += 1
                competitors.save()
        i += 1
        return render(request, 'hash_to_hash.html',
                  {'tag1': competitors.tag1, 'tag2': competitors.tag2, i: i, 'done': False})

    else:
        return render(request, 'hash_to_hash.html',
                  {'tag1': competitors.tag1, 'tag2': competitors.tag2, i: i, 'done': False})

What I want to do is, per visitor, randomize the ordering of the Competitors objects, and then iterate through that randomized list.

Questions:

1. What's a better way of randomizing things besides objects.order_by('?')? I'm using MySQL, and I've seen some things on here about how order_by('?') + MySQL = SLOOOOOOOW. There were a couple of suggestions given, and I could easily implement something (I was thinking something along the lines of random.shuffle(Competitors.objects.all())), but I'm not sure where I'd put it, which leads me to my second question...
2. How do I make sure the randomization only happens once? I don't want to bore people by making them review the same pairs over and over again, and I don't want to throw off my results by having some pairs randomly showing up more than once. I want everybody to see the same list, just in different orders.

I suspect the answer lies in a Manager class, but, really, this all boils down to my lack of knowledge about what Django calls when.

(I'm also having a problem where the results don't seem to be getting saved to my db, but that's a different, probably more easily solved, issue.)

Answer 1

Tried Greg's answer on PostgreSQL and got an error, because there are no random function with seed there. After some thinking, I went another way and gave that job to Python, which likes such tasks more:

def order_items_randomly(request, items):
    if not request.session.get('random_seed', False):
        request.session['random_seed'] = random.randint(1, 10000)
    seed = request.session['random_seed']
    random.seed(seed)
    items = list(items)
    random.shuffle(items)
    return items

Works quick enough on my 1.5k items queryset.

P.S. And as it is converting queryset to list, it's better to run this function just before pagination.

Answer 2

My solution, based largely on Greg's awesome suggestion above:

View:

def compare_hashes(request, i=0):
    i = int(i)
    competitors = Competitors.objects.all()
    DATABASE_ENGINE = settings.DATABASES['default']['ENGINE'].split('.')[-1]
    if DATABASE_ENGINE == 'mysql':
        if not request.session.get('random_seed',False):
            ints = xrange(10000)
            request.session['random_seed'] = sample(ints,1)[0]
        seed = request.session['random_seed']
        competitors = competitors.extra(select={'sort_key': 'RAND({})'.format(seed)})
        randomized_competitors = competitors.order_by('sort_key')
    try:
        chosen_competitor = randomized_competitors[i]
    except IndexError:
        return render(request, 'hash_to_hash.html',
        {'tag1': '', 'tag2': '', i: 0, 'done': True})

    if request.method == 'POST':
        form = CompetitorForm(request.POST)
        if form.is_valid():
            if "yes" in request.POST:
                competitors.votes += 1
                competitors.save()
            i += 1
    return render(request, 'hash_to_hash.html',
              {'tag1': chosen_competitor.tag1, 'tag2': chosen_competitor.tag2, 'action':'/hash/{}'.format(i), 'done': False})

Template (which uses Django-bootstrap-toolkit and still needs some work):

{% extends 'base.html' %}
{% load bootstrap_toolkit %}
{% block title %}Title{% endblock %}
{% block big_title %}Title{% endblock %}
{% block main-content %}
    <h3>Hash-to-Hash</h3>
    {% if done %}
    <div class="row-fluid">
        <div class="span8">
            <h4>You're Done!</h4>
            <p>Thanks so much!</p>
        </div>
    </div>
    {% else %}
        <div class="row-fluid">
            <div class="span6" id="tag1">
                <h4>{{ tag1.text }}</h4>
            </div>
            <div class="span6" id="tag2">
                <h4>{{ tag2.text }}</h4>
            </div>
        <div class="span8">
            <form action="{{ action }}" method="post">
           {% csrf_token %}
           <!-- {{ form|as_bootstrap }} -->
           {% bootstrap_form form layout="vertical" %}
           <div class="form-actions">
               <button type="submit" class="btn btn-success" name="yes">
                   YES, I think these two tags are co-referential
               </button>
               <button type="submit" class="btn btn-danger" name="no">
                   NO, I don't think these two tags are co-referential
               </button>
            </div>
            </form>
        </div>
    </div>
    {% endif %}
{% endblock %}

The URLconf looks like this: url(r'^hash/(\d*)$', compare_hashes)

Thanks again!

Answer 3

To maintain a consistent random order, you should order by a seeded random, with the seed stored in the session. Unfortunately you can't do this with pure django orm, but with mysql it's trivial:

import random
from django.conf import settings

# there might be a better way to do this...
DATABASE_ENGINE = settings.DATABASES[settings.DATABASES.keys()[0]]['ENGINE'].split('.')[-1]

def compare_hashes(request, i=None):
    competitors = Competitors.objects.all()

    if DATABASE_ENGINE == 'mysql':
        if not request.session.get('random_seed', False):
            request.session['random_seed'] = random.randint(1, 10000)
        seed = request.session['random_seed']
        competitors = competitors.extra(select={'sort_key': 'RAND(%s)' % seed}).order_by('sort_key')

    # now competitors is randomised but consistent for the session
    ...

I doubt performance would be an issue in most situations; if it is your best bet would be to create some indexed sort_key columns in your database which are updated periodically with random values, and order on one of those for the session.