django-oscar / django-oscar-api: huge number of queries to render the ProductSerializer

Question

I am using django-oscar with my own Product class, and a serializer for Django REST Framework. I am finding that adding some properties which are stored as attributes results in a huge number of queries.

For example as soon as I add "legacy_title" (which is an attribute I've added) to the serializer's fields, the number of queries made to render this result goes up from 3 to 70. I have 21 products in my databases.

from oscarapi.serializers.product import ProductSerializer as CoreProductSerializer


class ProductSerializer(CoreProductSerializer):
    class Meta(CoreProductSerializer.Meta):
        fields = (
            "url",
            "id",
            "description",
            "slug",
            "upc",
            "title",
            "structure",
            "legacy_title",
        )

My Product class:

from oscar.apps.catalogue.abstract_models import AbstractProduct


def attribute_error_as_none(callable_):
    try:
        return callable_()
    except AttributeError:
        return None


class Product(AbstractProduct):
    @property
    def legacy_title(self) -> str:
        return attribute_error_as_none(lambda: self.attr.legacy_title) or self.title

I'm using django-oscar-api's built-in ProductList view. If I add a few more fields which are backed by attributes, then the query count keeps going up, to 84 queries for these 21 products.

Then add things like children, recommendations, and images, and it takes 240 queries to render this result!

How can I get the query count under control? The endpoint to fetch all products is by far the slowest API endpoint in my entire backend and is becoming a problem. Should I override the ProductList view, with a custom queryset with some select_related or prefetch_related peppered in? I tried prefetch_related("attributes") but that only increases the query count by 1.

Answer 1

I brought the number of queries back under control by overriding django-oscar-api's ProductList.

from django.db.models import Prefetch
from oscar.core.loading import get_model
from oscarapi.views.product import ProductList as CoreProductList

ProductAttributeValue = get_model("catalogue", "ProductAttributeValue")


class ProductList(CoreProductList):
    def get_queryset(self):
        """
        Get the list of items for this view.
        This can be overridden in subclasses to provide custom behavior.
        """
        queryset = super().get_queryset()

        # Prefetch attributes to reduce number of queries
        queryset = queryset.select_related("parent", "product_class").prefetch_related(
            Prefetch(
                "attribute_values",
                queryset=ProductAttributeValue.objects.select_related("attribute", "value_option"),
                to_attr="prefetched_attr_values",
            ),
            "children",
            "images",
        )

        return queryset

To make use of these prefetched_attr_values, my Product model looks like this:

from oscar.apps.catalogue.abstract_models import AbstractProduct


def attribute_error_as_none(callable_):
    try:
        return callable_()
    except AttributeError:
        return None


def get_prefetched_attribute(product, attribute):
    if hasattr(product, "prefetched_attr_values"):
        # Use prefetched attributes to avoid extra queries
        return next(
            (
                attr_value.value
                for attr_value in product.prefetched_attr_values
                if attr_value.attribute.code == attribute
            ),
            None,
        )
    else:
        # Fall back to fetching attribute normally if not prefetched
        return attribute_error_as_none(lambda: getattr(product.attr, attribute))


class Product(AbstractProduct):
    @property
    def subtitle(self) -> str:
        return get_prefetched_attribute(self, "subtitle")

    @property
    def legacy_title(self) -> str:
        return get_prefetched_attribute(self, "legacy_title") or self.title