Создание рекурсивного запроса



  • Источник типа $recursive позволяет выполнить рекурсивный запрос, если СУБД SQL источника поддерживает выражения WITH RECURSIVE.

    Postgresql, например, поддерживает рекурсивные запросы и а логика их выполнения заключается в многократном объединении через UNION уже полученных результатов рекурсивного обхода и дополнительной выборки, полученной присоединением через INNER JOIN новых (дочерних или родительских) записей. Более подробно описано в документации к БД и примерах: https://www.postgresql.org/docs/9.1/queries-with.html? https://www.postgresqltutorial.com/postgresql-recursive-query/, https://habr.com/ru/post/269497/.

    Оператор $recursive включает аргументы:

    • $start - запрос, формирующий начальную выборку рекурсивного запроса, определяет начало обхода дерева (например, рутовые элементы или листовые); позволяет использовать только поля своего источника;
    • $joinedNext - запрос, формирующий дополнительную (дочернюю или родительскую) выборку, которая присоединяется к уже полученным результатам через INNER JOIN, после чего результаты объединяются через UNION ALL - эту операцию СУБД повторяет много раз, пока склейка результата и $joinedNext продолжает возвращать хотя бы одну запись (этому моменту требуется уделить особое внимание, чтобы не получилась бесконечная рекурсия); позволяет использовать поля своего источника и поля результата рекурсии, например для объединения или рекурсивных вычислений;
    • $filter - фильтр, используемый при склейке $joinedNext части, в 99% случаев соединяет предков с детьми и определяет направление обхода дерева (смотря от какого источника будут браться childId и parentId).

    Запросы $start и $joinedNext должны иметь одинаковые выходные поля, последовательность, названия и типы.

    Фильтр $filter, используемый внутри $start позволяет ограничить начальную выборку, например, только рутовыми элементами или листовыми. Фильтр внутри $joinedNext ограничивает только дополнительные выборки на текущей итерации рекурсии.

    Пример
    Составим запрос, который будет рекурсивно обходить все элементы, начиная с рутовых и формировать два дополнительных поля, уровень вложенности и массив с дочерними элементами.

    1. Создаем пустой срез и выбираем источник типа $recursive.
      create-recursive.png
    2. Добавляем источник начальной выборки, добавляем предварительные поля и добавляем фильтр, ограничивая выборку только рутовыми элементами (без родителя).
      select-roots.png
    3. В начальную выборку добавляем два дополнительных поля. Выражение для поля level для рутовых элементов устанавливается константой 0, а поле child формируется из массива с единственным идентификатором текущего рутового элемента.
      add-level-child-fields.png
    4. Задаем источник для дополнительной выборке $joinedNext и добавляем основные поля (выбираются поля текущего источника).
      select-next.png
    5. Вычисляем уровень текущего элемента, поле level: уровень родительского элемента + 1. Аналогично для поля child производится конкатенация массива родительского элемента и идентификатора текущего. Здесь поля level и child выбираются среди выходных полей рекурсии, при этом поле Id для конкатенации массива выбирается из текущего источника.
      select-rec-field.png
      calc-child.png
    6. Производим настройку фильтра пересечения результатов рекурсии и дополнительных наборов. При этом поле Id выбирается из выходных полей рекурсии, а поле ParentId из текущего источника. В таком случае обход будет выполняться сверху-вниз, чтобы дополнительная выборка включала элементы, у которых ParentId соответствует Id уже извлеченных элементов.
      rec-filter.png
    7. Выносим необходимые поля рекурсии в качестве выходных полей запроса. При необходимости, здесь так же может присутствовать агрегация и другие стандартные операторы запроса.
      output-fields.png

    Результат работы запроса:
    rec-results.png

    Запрос в формате json целиком:

    {
      "$from": {
        "$recursive": {
          "$start": {
            "$from": {
              "$slice": "c44ae54f01b7522e768ac3422e2484f0/f169fdd098491a67ff4b84ce8447744e"
            },
            "$filter": {
              "$and": [
                {
                  "$eq": [
                    {
                      "$field": "ParentId"
                    },
                    {
                      "$const": null,
                      "$type": "number"
                    }
                  ]
                }
              ]
            },
            "$select": {
              "Id": {
                "$field": "Id"
              },
              "Name": {
                "$field": "Name"
              },
              "Val": {
                "$field": "Val"
              },
              "ParentId": {
                "$field": "ParentId"
              },
              "level": {
                "$const": 0,
                "$type": "number"
              },
              "child": {
                "$concatArray": [
                  {
                    "$field": "Id"
                  }
                ]
              }
            },
            "$context": "subquery_9fa832fde581ba711072dc5a08506294"
          },
          "$joinedNext": {
            "$from": {
              "$slice": "c44ae54f01b7522e768ac3422e2484f0/f169fdd098491a67ff4b84ce8447744e"
            },
            "$select": {
              "Id": {
                "$field": "Id"
              },
              "Name": {
                "$field": "Name"
              },
              "Val": {
                "$field": "Val"
              },
              "ParentId": {
                "$field": "ParentId"
              },
              "level": {
                "$add": [
                  {
                    "$field": "level",
                    "$context": "c44ae54f01b7522e768ac3422e2484f0/b020f1bfa8c90ca81d06e4b0421a0c44"
                  },
                  {
                    "$const": 1,
                    "$type": "number"
                  }
                ]
              },
              "child": {
                "$arrayAppend": {
                  "$arr": {
                    "$field": "child",
                    "$context": "c44ae54f01b7522e768ac3422e2484f0/b020f1bfa8c90ca81d06e4b0421a0c44"
                  },
                  "$value": {
                    "$field": "Id"
                  }
                }
              }
            },
            "$context": "subquery_d902f114f22d87b19673ef9c069aeba1"
          },
          "$filter": {
            "$and": [
              {
                "$eq": [
                  {
                    "$field": "Id",
                    "$context": "c44ae54f01b7522e768ac3422e2484f0/b020f1bfa8c90ca81d06e4b0421a0c44"
                  },
                  {
                    "$field": "ParentId",
                    "$context": "subquery_d902f114f22d87b19673ef9c069aeba1"
                  }
                ]
              }
            ]
          }
        }
      },
      "$select": {
        "Id": {
          "$field": "Id"
        },
        "Name": {
          "$field": "Name"
        },
        "Val": {
          "$field": "Val"
        },
        "ParentId": {
          "$field": "ParentId"
        },
        "level": {
          "$field": "level"
        },
        "child": {
          "$field": "child"
        }
      },
      "$context": "c44ae54f01b7522e768ac3422e2484f0/b020f1bfa8c90ca81d06e4b0421a0c44"
    }
    

Log in to reply