Using manager method cause similar queries

I have Category and Restaurant models. In the main menu I list the categories and some selected restaurants associated with it.

I use the extra manager method to limit restaurants. However, I’ve found that this causes similar queries to occur.

My code is following from model to template. (It is simplified to read easily.)

Class Category(models.Model):

class RestaurantManager(models.Manager):
    def main_menu(self):
        return self.get_queryset().filter(is_selected=True).order_by('rank', '-updated_at')[:5]

Class Restaurant(models.Model):
	category = models.ForeignKey(Category, related_name='restaurants', on_delete=models.CASCADE)
    objects = RestaurantManager()

def main_context(request):
    return {
        'categories': Category.objects.prefetch_related('schools').order_by('order')

I’m using context processors because the context should take place in my base template, so I added this globally.

# base.html
{% for category in categories %}
    {{ category.label }}
    {% for restaurant in category.restaurants.main_menu %}
        {{ }}
    {% endfor %}
{% endfor %}

When I check the queries from debug_toolbar,

SELECT ••• FROM "core_restaurant" WHERE ("core_restaurant"."category_id" = 4 AND "core_restaurant"."is_selected") ORDER BY "core_restaurant"."rank" ASC, "core_restaurant"."updated_at" DESC LIMIT 5

6 similar queries.

But, if I get the restaurants without using manager method:

{% for restaurant in category.restaurants.all %}
    {{ }}
{% endfor %}

6 similar queries do not occur and the total number of queries is reduced by 6.
However, at this time all related restaurants are listed. I need to filter them as in manager method.

My question is:

How can I solve this similar query problem without breaking the structure I want to achieve?

Thanks in advance.

How frequently are these Restaurant models updated?

What we have done in a similar situation is cache the data for the menu, and only update the cache when the underlying data is updated. (But our underlying data for that menu only updates at most once per day.)

The value of doing just about anything else is really going to depend upon some specifics here, like the volume of data involved in each of these different models.

For example, you could consider running a query to retrieve all the restaurants ordered by category, rank, and -updated_at, and manually add the appropriate subset of each to their corresponding Category object. This will reduce the number of queries to two at the expense of some in-memory manipulation of the querysets.

Or, if you’re running this on PostgreSQL, you could use the ArraySubquery expression to directly annotate a list of name to the Category queryset. (Technically, this is only one query in that you’re only making one request to the database, but you’d want to look at an EXPLAIN to see how good or bad it is in your environment.)

1 Like