Django prefetch_related and select_related on parent table

Question

I have the following db structure:

catogory
   id
   name
   parent_id

class Category(models.Model)
  name = models.CharField(max_length=400, blank=True, null=True)
  parent = models.ForeignKey("self", blank=True, null=True)

I need to get all categories and their parent.

If I do this:

Category.objects.select_related("parent").filter(pk__in=[ids])

it will just return the parent of the first level.

How can I get parents of all levels with minimal db calls?

My approach is to create new objects of non-db models, for example: CategorySerializer, which will transfer these category models to non-db so that the logical level can use it

Answer 1

What if you created a function that you can call recursively before there are more parents left.

def get_categories(ids):
    ids = list(Category.objects.select_related("parent").filter(pk__in=[ids]).values_list('parent_id', flat=True))
    if len(ids) == 0:
        return []
    else:
        return ids + get_categories(ids)

So start with this call.

all_parent_ids = []
for category in list(Category.objects.all()):
    all_parent_ids.append(get_categories(category.parent_id))

, , , .