Как предотвратить N+1 запросы при получении предков с помощью Django Treebeard?

Мы используем материализованный путь Django Treebeard для моделирования организационной иерархии следующим образом:

Organizational Hierarchy Example

Теперь каждый узел в организационном дереве может иметь несколько задач:

class Organization(MP_Node):
    node_order_by = ['name']
    name = models.CharField(max_length=100)

class Task(models.Model):
    organization = models.ForeignKey(Organization, on_delete=models.CASCADE)
    description= models.TextField()

Получив список задач, мы хотим включить в результат полный организационный путь каждой задачи. Как мы можем добиться этого без необходимости N+1 запросов?

Ожидаемый результат для организации Фабрика 1 может быть, например, таким:

Task name Organization Path
Task 1 MyCompany/Factory 1/Maintenance
Task 2 MyCompany/Factory 1/Operations
Task 3 MyCompany/Factory 1
Task 4 MyCompany/Factory 1/Operations

django-treebeard хранит материализованный путь в колонке path в виде строки, как это: 000100020005002I. В этом примере следующие строки являются его предками (с учетом длины шага по умолчанию 4):

0001
00010002 
000100020005  
000100020005002I

Что делает django-treebeard, так это разбивает путь страницы на вышеупомянутые кусочки в Python, а затем выполняет запрос к базе данных следующим образом:

Organization.objects.filter(path__in=['0001', '00010002', '000100020005'])`

Чтобы избежать проблемы n+1 запроса, нам нужно избежать разделения пути в Python и выполнить поиск предков в базе данных с помощью подзапроса.

Соответствие шаблону может быть использовано для проверки того, содержится ли путь предка в пути ребенка: 00010002 соответствует 000100020005002I, когда путь кандидата используется в качестве шаблона для пути рассматриваемой организации:

000100020005002I LIKE 00010002%  --- equals true

SELECT  
  organization.path, 
  ARRAY(
   SELECT 
     name 
   FROM
     organization o_ 
   WHERE 
     organization.path LIKE o_.path || '%' 
  )
FROM 
  organization
organization.path array
0001 {root}
00010001 {root, org_a}
00010002 {root, org_b}
000100020001 {root, org_b, org_b1}

Django не предоставляет готового решения для переключения аргументов при поиске .filter(path__startswith='pattern') (как это требуется в нашем случае). Поэтому я использую выражение RawSQL.

>>> from django.db.models.expressions import RawSQL

>>> orgs = Organization.objects.annotate(
    ancestors=RawSQL(
        """
        ARRAY(
          SELECT name FROM organization o_ 
          WHERE organization.path LIKE o_.path || '%%'
        ) 
        FROM organization
        """,
        params=[],
    )
)

>>> orgs[0].ancestors
['Root', "Org 1", "Org 2", "Org 3"]
Вернуться на верх