How can I reduce the number of requests?

Question

This code currently executes about 50 SQL queries:

c = Category.objects.all() categories_w_rand_books = [] for category in c: r = Book.objects.filter(author__category=category).order_by('?')[:5] categories_w_rand_books.append((category, r)) 

I need to reduce the number of queries used to a minimum in order to speed things up and not cause a load on the server.

Basically, I have three models: Category, Author, Book. The author belongs to a category (not books), and I need to get a list of all categories with 5 random books under each.

Answer 1

If you prefer a single query and use MySQL , check out the excellent link provided by @Crazyshezy in his comment.
For PostgreSQL backends, a possible query is possible (provided that there are relations with zero FK values ​​from Book to Author and from Author to Category ):

 SELECT * FROM ( SELECT book_table.*, row_number() OVER (PARTITION BY category_id ORDER BY RANDOM()) AS rn FROM book_table INNER JOIN author_table ON book_table.author_id = author_table.id ) AS sq WHERE rn <= 5 

You can then wrap it inside a RawQuerySet to get Book instances

 from collections import defaultdict qs = Book.objects.raw("""The above sql suited for your tables...""") collection = defaultdict(list) for obj in qs: collection[obj.category_id].append(obj) categories_w_rand_books = [] for category in c: categories_w_rand_books.append((category, collection[category.id])) 

You may not want to run this request for each request directly without caching.

In addition, your code generates no more than 50 * 5 = 250 Book s, randomly, I'm just wondering why, because it seems that there is too much for one page. Are items displayed as tabs or something else? Perhaps you could reduce the number of SQL queries by executing Ajax or simplifying the requirement?

Update

To use book.author without running another query, try prefetch_related_objects

 from django.db.models.query import prefetch_related_objects qs = list(qs) # have to evaluate at first prefetch_related_objects(qs, ['author']) # now instances inside qs already contain cached author instances, and qs[0].author # will not trigger an extra query 

The above code preloads authors in a package and fills them in qs . It just adds another request.

Answer 2

I am not sure if this will help you because I don’t know the details and context of your problem, but using order_by('?') very inefficient, especially with some back-end DBs.

To display objects with some randomness, I use this approach using a special filter:

 @register.filter def random_iterator(list, k): import random class MyIterator: def __init__(self, obj, order): self.obj=obj self.cnt=0 self.order = order def __iter__(self): return self def next(self): try: result=self.obj.__getitem__(self.order[self.cnt]) self.cnt+=1 return result except IndexError: raise StopIteration if list is None: list = [] n = len(list) k = min(n, k) return MyIterator(list, random.sample(range(n), k)) 

The code in my Django view looks something like this:

 RAND_BOUND = 50 categories = Category.objects.filter(......)[RAND_BOUND] 

And I use it in my template as follows:

 {% for cat in categories|random_iterator:5 %} <li>{{ cat }}</li> {% endfor %} 

This code will select 5 random categories of the (given) RAND_BOUND set. This is not a perfect solution, but hope it helps.