Fetching all objects hierarchically nested by ForeignKey in one query

Question

I'm modeling a folders structure in my application. Every element (folder or file) has its own id and id of its parent:

class Folder(TimeStampedModel):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4)
    parent_folder = models.ForeignKey(
        'folders.Folder',
        on_delete=models.CASCADE,
        null=True,
    )

I want to fetch all nested children of a folder. Obviously it can be done by recursively querying "find all objects with this parent_id", calling this recursively on all the found objects. But I'm wondering if there is a possibility in Django to do this in one query, so that I don't have to connect to the database X times but only once. What is the best approach here?

If you are using Postgres you can use recursive common table expressions through raw sql: https://stackoverflow.com/questions/39511993/how-to-recursively-query-in-django-efficiently — Bernhard Vallant, Feb 06 '19 at 15:07
Maybe look into: https://github.com/django-mptt/django-mptt/ — bonidjukic, Feb 06 '19 at 15:08

Alouani Younes · Answer 1 · 2019-02-06T15:40:02.623

I suggest adding a new field in the model :

class Folder(TimeStampedModel):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4)
    path = CharField(max_length=200)
    parent_folder = models.ForeignKey(
        'folders.Folder',
        on_delete=models.CASCADE,
        null=True,
    )

For example if a file has a parent_1, and parent_1 has a parent_2, the your file object path will be saved as :

file.path = str(parent_2.id) + "/" + str(parent_1.id) + "/"

Where parent_2_id and parent_1_id are respectively the ids of the parent 2 and the parent 1.

To find all files included in parent_2 you will write : parent_2_id = str(parent_2.id)

Folder.objects.get(path__contains='parent_2_id')

To save a new file you will need the full path of the folder you save in :

file.path = parent_folder.path 
file.save()

As a side note: This is called "materialized path" and eg. implemented by django-treebeard: https://django-treebeard.readthedocs.io/en/latest/mp_tree.html — Bernhard Vallant, Feb 06 '19 at 15:40

Fetching all objects hierarchically nested by ForeignKey in one query

1 Answers1