Bill Noble Bill Noble - 6 months ago 16
SQL Question

Django code to get IDs from queryset, shuffle IDs, and query database in shuffled order

I am trying to do the following in Django:


  1. Do a database query to return the IDs of all records that satisfy the query.

  2. Shuffle the list of IDs from the query into a random order.

  3. Create a new queryset that gets records from the database in the shuffled order.



So if my initial database query looks like this:

queryset = Item.objects.filter(my_field = a_value)


How can I get at a list of IDs from the query so that I can shuffle with:

random.shuffle(list_of_ids_matching_query)


I.e. I want to create the
list_of_ids_matching_query
from the queryset, but I can't work out how to do that.

I then plan to do a query along the lines of:

queryset = Item.objects.filter(pk__in=list_of_ids_matching_query)


But I need the query to return records in the same order as the IDs in the shuffled list. Will it do that? Or do I have to extract the records from the database one at a time.

What I am trying to do, and there may well be a better way to do this, is to get a random set of results in random order from the database.

Answer

Try this query

list_of_ids_matching_query = list(Item.objects.filter(my_field = a_value).values_list('id', flat=True))

For getting records in the same order as the IDs in the shuffled list,

ordering = 'FIELD(`id`, %s)' % ','.join(str(id) for id in shuffiled_list)
Item.objects.filter(pk__in=shuffiled_list).extra(select={'ordering': ordering }, order_by=('ordering', ))
Comments