Django, fast way for random record with aggregates

Question

So I have the following query:

Person.objects.annotate(film_count=Count('film')).filter(film_count__gte=3).order_by('?')[0]

Which pulls a random person that has 3 films or more. However, as stated in the django documents (https://docs.djangoproject.com/en/dev/ref/models/querysets/#order-by-fields) this approach with the ('?') is very slow and I plan for this query to be used frequently by users.

I suppose one way to do it is to fetch all the primary keys generated by the full list of that query, and then store it in a txt file and randomly select one each time. But I'm wondering if there is a more elegant solution?

I guess another way is to do:

Person.objects.annotate(film_count=Count('film')).filter(film_count__gte=3).get(pk=randint(1,num_persons))

where num_persons is the number of people in my database, and if the person does not match the query and throws a DoesNotExist error I simply run it again.

Answer 1

You can use the simplest solution: count the rows and select one at random:

queryset = Person.objects.annotate(film_count=Count('film')).filter(film_count__gte=3)
count = queryset.count()
result = queryset[random.randint(count)]

Note, however, that this approach might fail if some rows are deleted before lines 2 and 3 of the snippet (so you might wrap last line in a try-catch with retry)