Retrieving random records from database depending on an attribute value until a limit, and complete with other if the limit is not reached

Question

I'm using Django with Postgres.

On a page I can show a list of featured items, let's say 10.

1. If in the database I have more featured items than 10, I want to get them random/(better rotate).

2. If the number of featured item is lower than 10, get all featured item and add to the list until 10 non-featured items.

Because the random takes more time on database, I do the sampling in python:

count = Item.objects.filter(is_featured=True).count()

        if count >= 10:
            item = random.sample(list(Item.objects.filter(is_featured=True))[:10])
        else:
            item = list(Item.objects.all()[:10])

The code above miss the case where there less than 10 featured(for example 8, to add 2 non-featured).

I can try to add a new query, but I don't know if this is an efficient retrive, using 4-5 queries for this.

Answer 1

The best solution I could find is this:

 from itertools import chain
 items = list(chain(Item.objects.filter(is_featured=True).order_by('?'), Item.objects.filter(is_featured=False).order_by('?')))[:10]

In this way, the order of the querysets are retained, but downside is that items becomes a list not a Queryset. You can see more details in this SO Answer. FYI: there are some fantastic solutions like using Q or pipe but they don't retain order of queryset.

Answer 2

SQL method: You can achieve that with an SQL statement like this:

SELECT      uuid_generate_v4(), *
FROM        table_name
ORDER BY    NOT is_featured, uuid_generate_v4()
LIMIT 10;

Explain: The generated UUID should simulate randomness (for the purpose of e-commerce, this should suffice). While sorting the rows by NOT is_featured will put the is_featured rows on top; and automatically flow the rows down to 10 limits if it run out of featured items.