CODEWITHSUNDEEP
pythonsqldjangopostgresqlrandom
Chase Roberts
Chase Roberts

Reputation: 9376

Add an element of randomness to ordering in SQL, django

I am using Python/Django but I am suspecting that this is going to have to be done on the mysql level.

If I have a model such as:

class Article(models.Model):
    title = models.CharField(max_length = 256)
    rating = models.IntegerField(default=0)

I want to query for articles with the highest ratings but I want to add an element of randomness. I don't just want all of them to have ratings of 100 out of 100.

If I was doing this in python I would do something like

articles = Article.objects.all()
#multiply each rating by a random multiplier and store that value in a tuple with the article
articles = [(random.random()*article.rating, article) for article in articles] 
#sort by the calculated rating*random value
articles.sort(key=lambda tup: tup[0], reverse=True)

And maybe that is the best I can do. But if I was going to take this down to the query level, this is about all I have so far:

articles = Article.objects.extra(select={"rand_rating":'( rating * {})'.format( random.random() )})
articles = articles.extra(order_by=['rand_rating'])

Which at first glance I thought might work, but the random.random() number is just going to be multiplying everything by the same constant which is essentially useless. I looked into the sql RAND() but from what I have read, I will have the same problem as the RAND() isn't called for each row, but rather just once per query.

Upvotes: 0

Views: 49

Answers (1)

G Rice
G Rice

Reputation: 176

There is a random ordering built into .order_by() using the string '?':

https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.order_by

In your case it would be:

Article.objects.order_by('?')

After ordering them, you might then filter out the top X records and again order them by rating.

Upvotes: 1

Related Questions