How to speed up tastypie's queries with ToManyField

Question

In resources.py I have:

class CategoryResource(ModelResource):
    items = fields.ToManyField('ItemResource', 'items', full=True, null=False, readonly=True, related_name='items')
    class Meta:
        queryset = Category.objects.all().order_by('id')
        include_resource_uri = False
        always_return_data = True
        resource_name = 'category'

There are about 5000 items in 6 categories. When I am make 'list' api request i.e. api/1.0/category, it makes about 5000 queries to database. How can I optimize it? I know about full=False, but it isn't suit my needs.

UPD: I found what caused so much queries. I have 'categories' relation in ItemResource, so tastypie generate a select query for each item.

class ItemResource(ModelResource):
    categories = fields.ToManyField(CategoryResource, 'categories', null=True, readonly=True)

    def dehydrate_categories(self, bundle):
        categories = Category.objects.filter(owner_id=bundle.request.user.id, items__item=bundle.obj)
        return [category.name for category in categories]

Obviously that is unnecessary data when I requesting a CategoryResource, is there any way to exclude it from queries?

Answer 1

Try this:

def get_object_list(self, request):
    return super(CategoryResource, self).get_object_list(request) \
        .prefetch_related('items', 'items__categories')

Do not use select_related, because it return duplicate rows.

prefetch_related made one query, that returns all items and Django ORM match it to proper rows.

EDIT

Change dehydrate_categories

def dehydrate_categories(self, bundle):
    return [category.name for category in bundle.obj.categories.all() if category.owner == bundle.request.user]

Answer 2

If there's still a problem, it could be that ItemResource also has one or more related fields. If that's the case, you can probably do something like:

def get_object_list(self, request):
    return super(CategoryResource, self).get_object_list(request).prefetch_related('items__relatedfield1', 'items__relatedfield2')

Keep in mind, any queryset alterations such as select_related or prefetch_related performed on ItemResource won't affect the query for related models on CategoryResource.

UPDATE: Try something like this:

class CategoryResource(ModelResource):
    items = fields.ToManyField('ItemResource', 'items', full=True, null=False, readonly=True, related_name='items')

    class Meta:
        queryset = Category.objects.all().order_by('id')
        include_resource_uri = False
        always_return_data = True
        resource_name = 'category'

    def get_object_list(self, request):
        return super(CategoryResource, self).get_object_list(request) \
            .prefetch_related('items', 'items__categories')

class ItemResource(ModelResource):
    categories = fields.ToManyField(CategoryResource, 'categories', null=True, readonly=True)

    def dehydrate_categories(self, bundle):
        categories = items__item=bundle.obj.categories.all()
        return [
            category.name
            for category in categories
            if category.owner_id == bundle.request.user.id
        ]

OR:

class CategoryResource(ModelResource):
    items = fields.ToManyField('ItemResource', 'items', full=True, null=False, readonly=True, related_name='items')

    class Meta:
        queryset = Category.objects.all().order_by('id')
        include_resource_uri = False
        always_return_data = True
        resource_name = 'category'

    def get_object_list(self, request):
        return super(CategoryResource, self).get_object_list(request) \
            .prefetch_related(
                'items',
                Prefetch('items__categories', queryset=Category.objects.filter(owner_id=bundle.request.user.id))
            )

class ItemResource(ModelResource):
    categories = fields.ToManyField(CategoryResource, 'categories', null=True, readonly=True)

    def get_object_list(self, request):
        return super(ItemResource, self).get_object_list(request) \
            .prefetch_related(
                Prefetch('categories', queryset=Category.objects.filter(owner_id=bundle.request.user.id))
            )

    def dehydrate_categories(self, bundle):
        return [
            category.name
            for category in bundle.obj.categories.all()
        ]

Also, you can probably use a ListField instead of ToManyField for ItemResource.categories since you're manually handling the dehydration.