Postgres order by rand

order_by_random.py

"""
This is to allow grabing a random set of rows,
in a repeatable way (for example, if you need to ensure that
a user always sees the same "random" set of results).

Method one sets the seed in Postgres.
It's using order by RANDOM(), so it can be extremely slow
for large querysets.

Method two is significantly faster, as we are generating
the random ids in python, but it will only work properly
if the ids are sequential.
A workaround, if there can be some occasional small gaps,
would be selecting more results than needed and use the extras
to fill any holes (not demonstrated here but not too hard to do).
"""

def using_setseed(queryset):
    """ Simple but horribly slow, also returns a RawQuerySet. """
    raw_sql = str(queryset.extra(select={'sort_key': 'random()'})
                 .order_by('sort_key').query)
    set_seed = "SELECT setseed(%s);" % float(random_seed)
    return queryset.model.objects.raw(set_seed + raw_sql)
    
    
def using_id_in(queryset, random_seed):
    """ Faster but kind of ugly SQL. 
        Ordering is done with:
            ORDER BY
                CASE
                    WHEN id=10 THEN 0
                    WHEN id=2 THEN 1
                    WHEN id=1 THEN 2
                    ...
                END;

    """
    random.seed(random_seed)
    last_id = queryset.order_by('-id')[0].id
    random_ids = random.sample(xrange(1, last_id + 1), last_id)
    clauses = ' '.join([
        'WHEN id=%s THEN %s' % (pk, i)
        for i, pk in enumerate(random_ids)
    ])
    ordering = 'CASE %s END' % clauses
    queryset = queryset.filter(id__in=random_ids).extra(
        select={'ordering': ordering}, order_by=('ordering',))
    return queryset

Hi,
I've applied following function too. But i am getting an error.

LINE 1: SELECT COUNT(*) FROM (SELECT (CASE WHEN id=152 THEN 0 WHEN i...

Here is my full code.

class TestAdmin(admin.ModelAdmin):
    list_display = ('name',)

    def get_queryset(self, request):
        qs = super(TestAdmin, self).queryset(request)
        sorted_list = natsort.natsorted(qs, key=operator.attrgetter('name'))
        pk_list = [obj.pk for obj in sorted_list]
        # pk_list is generating following list
        # pk_list = [152, 153, 154, 165, 195, 2, 3, 200, 81, 148, 92, 205, 226, 206, 82, 132, 145, 159, 127, 131, 203, 198, 149, 217, 218, 219, 213, 214, 215, 216, 220, 221, 156, 128, 129, 157, 142, 139, 138, 164, 172, 181, 193, 229, 151, 155, 38, 7, 9, 140, 182, 189, 190, 199, 201, 207, 208, 211, 224, 228, 230, 10,
        #93, 174, 173, 135, 86, 134, 150, 21, 22, 23, 76, 161, 162, 12, 13,
        #15, 16, 17, 74, 75, 99, 100, 101, 102, 33, 35, 36, 19, 106, 39, 107,
        #88, 20, 109, 147, 158, 166, 167, 168, 169, 186, 143, 83, 40, 179,
        #175, 130, 171, 4, 170, 177, 178, 180, 185, 191, 192, 194, 196, 197,
        #202, 204, 210, 212, 222, 223, 225, 227]
        clauses = ' '.join(['WHEN id=%s THEN %s' % (pk, i)
                            for i, pk in enumerate(pk_list)])
        ordering = 'CASE %s END' % clauses
        queryset = qs.filter(pk__in=pk_list).extra(
            select={'ordering': ordering}, order_by=('ordering',))
        return queryset

Can you please help me? thanks a lot.