В продолжении статьи "Идеальный каталог, набросок архитектуры", я покажу на примерах как можно использовать предложенную структуру БД для хранения произвольных данных и выполнения произвольных поисков по этим данным. Скрипты лежат в репозитории — universal_data_catalog_idea.

Приглашаю под кат, тех кому интересно посмотреть на эти скрипты с авторскими комментариями.

Содержание репозитория

В репозитории полный набор инфраструктурных скриптов:

1. создание таблиц
2. заполнения данными;
3. очистки таблиц от данных;
4. удаления всех созданных таблиц;

Когда я начинал писать скрипты их объём был небольшим, но когда я добрался до создания тестовых наборов, объём кода вырос до 1000+ строк. Такие большие скрипты вставлять в статью мне кажется излишним, поэтому если вам хочется потрогать «живые» данные, то клонируйте репозиторий и смотрите как оно в жизни получается.

В схеме БД я сделал небольшие изменения — редакторов (redactor_id) вынес из контента (content), теперь контент сам по себе, редакторы сами по себе.

Основные моменты организации данных

Сама по себе система не диктует правил использования, логика к ней может быть применена любая.

Основное назначение системы это поиск данных, хранение данных — это необходимый, но тем не менее второстепенный функционал.

Хранить можно любые данные, любой конфигурации. Данные каталога состоят из Сущностей (item) и их Значений (content). Каждое Значение, является значением конкретной Характеристики (property). Сущности с одним и тем же набором Характеристик можно объединить в Рубрики (rubric).

Сущность может принадлежать одной Рубрике, может — нескольким, в представленном варианте — только одной.

Добавление данных (хранение)

Как добавлять данные в каталог можно посмотреть в \deploy\commit_dml.sql. Последовательность такая:

1. создать Рубрики (rubric);
2. если необходима иерархия Рубрик, то создать Иерархию (element_tree) и распределить рубрики по иерархии (rubric_element_tree);
3. добавить Характеристики (property);
4. если предполагается использование Характеристик в соответствии с какими либо правилами, то можно добавить Опции этих правил (tag) и соответствующим образом стыковать Характеристики и Опции (property_tag);
5. назначить Рубрикам Характеристики (rubric_property);
6. добавить Сущности (item);
7. сгруппировать Сущности по Рубрикам (rubric_item);
8. добавить Значения (content) для Характеристик;
9. стыковать Значения с Сущностями (item_content);
10. если предполагается что у Значений будет несколько редакторов, то добавить Редакторов (redactor) и назначить Значениям Редакторов (redactor_content);
11. если предполагается не только строковый поиск, то конвертировать пользовательский ввод (content.raw) в конкретный тип данных и записать данные в соответствующую таблицу (date_matter.date_time, digital_matter.digital, duration_matter.duration, string_matter.string);

Список вышел длинным, но по факту все шаги укладываются в два действия:

1. добавить Сущность;
2. задать Значения;

Остальные шаги по необходимости. С теорией хранения информации разобрались. Теперь практическое применение.

Практическое наполнение каталога

Допустим мы хотим сделать своё «авито» для продажи экскаваторов.
Для этого мы добавим корневую рубрику «Экскаваторы», и к ней две дочерних «Экскаваторы карьерные» и «Экскаваторы погрузчики».
Для рубрики «Экскаваторы карьерные» назначаем свойства:
«Модель изделия»;
«Торговая марка»;
«Ёмкость ковша»;
«цена товара в рублях»;
«единицы измерения для товарной позиции»;
Для рубрики «Экскаваторы погрузчики» назначаем аналогичный набор свойств плюс свойство «Ёмкость лопаты».
Характеристики:
«Модель изделия»,
«Торговая марка»,
«Ёмкость ковша»,
«Ёмкость лопаты»,
являются системными, значения этих Характеристик задаёт контент менеджер, назначаем им Опцию — «SYSTEM_PROPERTY».
«Ёмкость ковша» и «Ёмкость лопаты» это числовые данные — Опция «DIGITAL_DATA_TYPE», поиск по ним будет поиском по диапазону значений — «BETWEEN_SEARCH_TYPE».
«Модель изделия» и «Торговая марка» это строковые данные — «STRING_DATA_TYPE», поиск по Характеристике «Торговая марка» будет как по справочнику производителей — перечислением — «ENUMERATION_SEARCH_TYPE», по Характеристике «Модель изделия» будем искать вхождение подстроки — «LIKE_SEARCH_TYPE».

Забиваем Сущности и Значения. Теперь можно выполнить общий поиск.

Общий поиск

Общий поиск подразумевает под собой поиск везде, наше везде ограничено только Рубриками и Сущностями, в принципе можно искать ещё и в Значениях, благо они хранят пользовательский ввод в формате строк. Поиск везде подразумевает поиск подстроки, в каких колонках? Видимо только «title» и «description».

Поехали!

-- Поиск "везде" ( в рубриках и в позициях каталога )
SELECT
  'RUBRIC',
  rr.code,
  rr.title,
  rr.description
FROM rubric rr
WHERE
  (rr.title ILIKE '%' || :SEARCH_PATTERN || '%'
   OR rr.description ILIKE '%' || :SEARCH_PATTERN || '%')
  AND EXISTS
  (
      SELECT NULL
      FROM
        (
          WITH RECURSIVE road_map ( id, element_tree_id, code, horizont ) AS
          (
            SELECT
              cet.id              AS id,
              cet.element_tree_id AS element_tree_id,
              r.code              AS code,
              0                   AS horizont
            FROM
              element_tree cet
              LEFT JOIN rubric_element_tree ret
                ON cet.id = ret.element_tree_id
              LEFT JOIN rubric r
                ON ret.rubric_id = r.id
            WHERE r.code = rr.code
            UNION
            SELECT
              pet.id,
              pet.element_tree_id,
              r.code,
              horizont + 1
            FROM
              element_tree pet
              JOIN road_map c
                ON (c.element_tree_id = pet.id)
              LEFT JOIN rubric_element_tree ret
                ON pet.id = ret.element_tree_id
              LEFT JOIN rubric r
                ON ret.rubric_id = r.id
          )
          SELECT NULL
          FROM
            road_map rm
          WHERE
            rm.code = :CATALOG_ROOT
          ORDER BY
            horizont DESC
          LIMIT 1
        ) R
  )
UNION
SELECT
  'ITEM',
  i.code,
  i.title,
  i.description
FROM
  rubric rr
  JOIN rubric_item ri
    ON rr.id = ri.rubric_id
  JOIN item i
    ON ri.item_id = i.id
WHERE
  (i.title ILIKE '%' || :SEARCH_PATTERN || '%'
   OR i.description ILIKE '%' || :SEARCH_PATTERN || '%')
  AND EXISTS
  (
      SELECT NULL
      FROM
        (
          WITH RECURSIVE road_map ( id, element_tree_id, code, horizont ) AS
          (
            SELECT
              cet.id              AS id,
              cet.element_tree_id AS element_tree_id,
              r.code              AS code,
              0                   AS horizont
            FROM
              element_tree cet
              LEFT JOIN rubric_element_tree ret
                ON cet.id = ret.element_tree_id
              LEFT JOIN rubric r
                ON ret.rubric_id = r.id
            WHERE r.code = rr.code
            UNION
            SELECT
              pet.id,
              pet.element_tree_id,
              r.code,
              horizont + 1
            FROM
              element_tree pet
              JOIN road_map c
                ON (c.element_tree_id = pet.id)
              LEFT JOIN rubric_element_tree ret
                ON pet.id = ret.element_tree_id
              LEFT JOIN rubric r
                ON ret.rubric_id = r.id
          )
          SELECT NULL
          FROM
            road_map rm
          WHERE
            rm.code = :CATALOG_ROOT
          ORDER BY
            horizont DESC
          LIMIT 1
        ) R
  );

Параметры запроса

Корневая рубрика

:CATALOG_ROOT

Код корневой Рубрики (rubric.code), служит для ограничения области поиска, можем искать в «Экскаваторах» вообще, а можем искать только в карьерных, или можем искать в товарах вообще, а можем только в экскаваторах.

Определение, имеет ли очередной объект в предках заданную Рубрику, происходит через иерархический запрос специфичный для каждой СУБД.

Можно искать без ограничения по области, тогда если мы добавили в наш каталог услуги (например по аренде экскаваторов) и резюме (например машиниста экскаватора) и ищем подстроку «экскаватор», то у нас в результаты вываляться и позиции по аренде экскаваторов и позиции про машиниста экскаватора.

Строка поиска

:SEARCH_PATTERN

Строка поиска — собственно та подстрока которую будем искать, можно строку поиска разбить по пробелам (или любому другому разделителю) и искать с условием:

WHERE
  (i.title ILIKE '%' || :PATTERN_PART1|| '%'
   OR i.description ILIKE '%' || :PATTERN_PART1 || '%')
AND 
  (i.title ILIKE '%' || :PATTERN_PART2|| '%'
   OR i.description ILIKE '%' || :PATTERN_PART2|| '%')
-- сколько угодно ещё частей строки поиска
AND 
  (i.title ILIKE '%' || :PATTERN_PART_N|| '%'
   OR i.description ILIKE '%' || :PATTERN_PART_N|| '%')

Получившийся скрипт конечно выглядит «гигантским», но пишется он не в ручную, пишет его наше приложение, а СУБД проглотит любой скрипт, поэтому не надо смотреть на количество букв — это не существенно.

Допустим с помощью этого поиска пользователь нашёл нужную ему рубрику, теперь в рубрике надо найти интересную позицию, для этого надо в Рубрике сделать поиск среди Сущностей по заданным параметрам.

Поиск по параметрам

Что бы пользователь представлял себе границы поиска, для него надо эти границы вычислить.
Если мы делаем поиск по рубрике, то показываем параметры (границы) поиска только для системных Характеристик.

Для этого посмотрим какие у нашей рубрики, есть системные Характеристики и какие способы поиска заданы:

-- Для поиска по рубрике необходимо сформировать параметры поиска - диапазоны допустимых значений для системных свойств
SELECT
  btrim(p.code) AS "property",
  btrim(tu.code) AS "author_type",
  btrim(ts.code) AS "search_type",
  btrim(tt.code) AS "data_type"
FROM
  rubric r
  JOIN rubric_property rp
    ON rp.rubric_id = r.id
  JOIN property p
    ON rp.property_id = p.id
  JOIN property_tag ptu
    on p.id = ptu.property_id
  JOIN tag tu
    on ptu.tag_id = tu.id

  JOIN property_tag pts
    on p.id = pts.property_id
  JOIN tag ts
    on pts.tag_id = ts.id

  JOIN property_tag ptt
    on p.id = ptt.property_id
  JOIN tag tt
    on ptt.tag_id = tt.id
WHERE
  r.code = 'ekskavatory-karernye'
  AND tu.code = 'SYSTEM_PROPERTY'
  AND ts.code IN ('LIKE_SEARCH_TYPE','BETWEEN_SEARCH_TYPE','ENUMERATION_SEARCH_TYPE')
  AND tt.code IN ('DIGITAL_DATA_TYPE','STRING_DATA_TYPE')
;
/*
MANUFACTURER_MODEL, SYSTEM_PROPERTY, LIKE_SEARCH_TYPE, STRING_DATA_TYPE
TRADE_MARK, SYSTEM_PROPERTY, ENUMERATION_SEARCH_TYPE, STRING_DATA_TYPE
BUCKET_CAPACITY_M3, SYSTEM_PROPERTY, BETWEEN_SEARCH_TYPE, DIGITAL_DATA_TYPE
*/

Пояснение к запросу

Смотрим какие Характеристик являются системными:

tu.code = 'SYSTEM_PROPERTY'

Смотрим какой тип поиска определён для этих Характеристик:

ts.code IN ('LIKE_SEARCH_TYPE','BETWEEN_SEARCH_TYPE','ENUMERATION_SEARCH_TYPE')

Смотрим тип данных для этих Характеристик:

tt.code IN ('DIGITAL_DATA_TYPE','STRING_DATA_TYPE')

Если один из трех параметров поиска для Характеристики не задан, то поиск выполнить не возможно (соединение таблиц tu ts tt через JOIN).

Определяем свойства для рубрики «Экскаваторы карьерные».

r.code = 'ekskavatory-karernye'

Результат запроса

В итоге получаем три Характеристики и параметры поиска:

1. MANUFACTURER_MODEL, SYSTEM_PROPERTY, LIKE_SEARCH_TYPE, STRING_DATA_TYPE
2. TRADE_MARK, SYSTEM_PROPERTY,ENUMERATION_SEARCH_TYPE, STRING_DATA_TYPE
3. BUCKET_CAPACITY_M3, SYSTEM_PROPERTY, BETWEEN_SEARCH_TYPE, DIGITAL_DATA_TYPE

Теперь для каждой Характеристики ищем границы.

Вычисление границ поиска

Вычисление границ поиска для «MANUFACTURER_MODEL»

Для «MANUFACTURER_MODEL» — тип поиска по вхождению («LIKE_SEARCH_TYPE») — границы не вычисляем, пользователю выводим поле для ввода строки поиска по этой Характеристике.

Вычисление границ поиска для «TRADE_MARK»

-- формирование параметров поиска
SELECT
  sm.string
FROM
  rubric r
  JOIN rubric_item ri
    ON r.id = ri.rubric_id
  JOIN item i
    ON ri.item_id = i.id
  JOIN item_content ic
    ON i.id = ic.item_id
  JOIN content c
    ON ic.content_id = c.id
  JOIN property p
    ON c.property_id = p.id
  JOIN string_matter sm
    ON c.id = sm.content_id
WHERE
  p.code = 'TRADE_MARK'
  AND r.code = 'ekskavatory-karernye'
GROUP BY sm.string;
/*
Уралмаш
Донэкс
*/

Тип данных — «STRING_DATA_TYPE» — значит анализируем — string_matter.string. Способ поиска — «ENUMERATION_SEARCH_TYPE» — значит делаем «GROUP BY». Вычислили два значения — «Уралмаш» и «Донэкс», пользователю выводим два чекбокса.

Вычисление границ поиска для «BUCKET_CAPACITY_M3»

-- формирование параметров поиска
SELECT
  max(dm.digital) AS maximum,
  min(dm.digital) AS minimum
FROM
  rubric r
  JOIN rubric_item ri
    ON r.id = ri.rubric_id
  JOIN item i
    ON ri.item_id = i.id
  JOIN item_content ic
    ON i.id = ic.item_id
  JOIN content c
    ON ic.content_id = c.id
  JOIN property p
    ON c.property_id = p.id
  JOIN digital_matter dm
    ON c.id = dm.content_id
WHERE
  p.code = 'BUCKET_CAPACITY_M3'
  AND r.code = 'ekskavatory-karernye';
/*
25,0.75
*/

Тип данных — «DIGITAL_DATA_TYPE» — значит анализируем — digital_matter.digital. Способ поиска — «BETWEEN_SEARCH_TYPE» — значит делаем MIN() и MAX(). Вычислили границы от 0.75 до 25, пользователю выводим что то такое:

<input type="range" min="0.75" max="25">

Поиск по параметрам

-- поиск по рубрике
/*
'ekskavatory-karernye'
'MANUFACTURER_MODEL'
'12'
'TRADE_MARK'
'Уралмаш'
'Донэкс'
'BUCKET_CAPACITY_M3'
0.75
25
 */
SELECT
  i.code
FROM
  rubric_item ri
  join rubric r
  on ri.rubric_id = r.id
  JOIN item i
    ON ri.item_id = i.id
  JOIN item_content ic
    ON i.id = ic.item_id
  JOIN content c
    ON ic.content_id = c.id
  JOIN string_matter sm
    ON c.id = sm.content_id
  JOIN rubric_property rp
    ON ri.rubric_id = rp.rubric_id
  JOIN property p
    ON c.property_id = p.id
       AND p.id = rp.property_id
WHERE
  r.code = :CODE
  AND p.code = :MODEL_PROPERTY
  AND sm.string LIKE '%'||:MODEL_LIKE||'%'
INTERSECT
SELECT
  i.code
FROM
  rubric_item ri
  join rubric r
  on ri.rubric_id = r.id
  JOIN item i
    ON ri.item_id = i.id
  JOIN item_content ic
    ON i.id = ic.item_id
  JOIN content c
    ON ic.content_id = c.id
  JOIN string_matter sm
    ON c.id = sm.content_id
  JOIN rubric_property rp
    ON ri.rubric_id = rp.rubric_id
  JOIN property p
    ON c.property_id = p.id
       AND p.id = rp.property_id
WHERE
    r.code = :CODE
  AND p.code = :MARK_PROPERTY
  AND sm.string IN ( :MARK1 , :MARK2)
INTERSECT
SELECT
  i.code
FROM
  rubric_item ri
  join rubric r
  on ri.rubric_id = r.id
  JOIN item i
    ON ri.item_id = i.id
  JOIN item_content ic
    ON i.id = ic.item_id
  JOIN content c
    ON ic.content_id = c.id
  JOIN digital_matter dm
    ON c.id = dm.content_id
  JOIN rubric_property rp
    ON ri.rubric_id = rp.rubric_id
  JOIN property p
    ON c.property_id = p.id
       AND p.id = rp.property_id
WHERE
    r.code = :CODE
  AND p.code = :BUCKET_PROPERTY
  AND dm.digital BETWEEN :MIN_BUCKET AND :MAX_BUCKET
;

По каждой Характеристике делаем поиск в соответствии с условиями заданными пользователем и выбираем те Сущности которые удовлетворяют всем условиям, то есть делаем пересечение — INTERSECT — результатов каждого отдельного поиска друг с другом.

Если пользователь задаст одно условие то поиск будет по одной Характеристике, если 100 условий, то будет выполнено 100 подзапросов и пользователю будут выданы те Сущности которые присутствуют в результатах каждого из 100 подзапросов.

В результате поиска по параметрам пользователь определил интересную ему позицию (Сущность).
Надо заметить что по хорошему поиск надо было выполнять с отбором по пользователю «SYSTEM», но я этот момент упустил.

Поиск по пользовательским значениям

Напоминаю что мы делаем «авито» для экскаваторов. То есть каждая позиция каталога имеет свою цену, и у каждого пользователя своя цена. И когда наш пользователь открыл карточку позиции каталога он увидел несколько предложений и соответственно захотел выполнить поиск по этим предложениям.

Были у нас свойства:

• «цена товара в рублях»;
• «единицы измерения для товарной позиции»;

Это пользовательские свойства, их значения задают пользователи, регулируется это Опцией «USER_PROPERTY».

Границы поиска

Определяем набор пользовательских Характеристик для рубрики «Экскаваторы погрузчики»:

SELECT
  btrim(p.code) AS "property",
  btrim(tu.code) AS "author_type",
  btrim(ts.code) AS "search_type",
  btrim(tt.code) AS "data_type"
FROM
  rubric r
  JOIN rubric_property rp
    ON rp.rubric_id = r.id
  JOIN property p
    ON rp.property_id = p.id
  JOIN property_tag ptu
    on p.id = ptu.property_id
  JOIN tag tu
    on ptu.tag_id = tu.id

  JOIN property_tag pts
    on p.id = pts.property_id
  JOIN tag ts
    on pts.tag_id = ts.id

  JOIN property_tag ptt
    on p.id = ptt.property_id
  JOIN tag tt
    on ptt.tag_id = tt.id
WHERE
  r.code = 'ekskavatory-pogruzchiki'
  AND tu.code = 'USER_PROPERTY'
  AND ts.code IN ('LIKE_SEARCH_TYPE','BETWEEN_SEARCH_TYPE','ENUMERATION_SEARCH_TYPE')
  AND tt.code IN ('DIGITAL_DATA_TYPE','STRING_DATA_TYPE')
;
/*
GOODS_ITEM_PRICE_RUB,USER_PROPERTY,BETWEEN_SEARCH_TYPE,DIGITAL_DATA_TYPE
GOODS_ITEM_UNITS_OF_MEASURE,USER_PROPERTY,ENUMERATION_SEARCH_TYPE,STRING_DATA_TYPE
*/

«GOODS_ITEM_PRICE_RUB»:

• «BETWEEN_SEARCH_TYPE» — поиск по диапазону;
• «DIGITAL_DATA_TYPE» — числовые данные;

«GOODS_ITEM_UNITS_OF_MEASURE»:

• «ENUMERATION_SEARCH_TYPE» — поиск с перечислением;
• «STRING_DATA_TYPE» — символьные данные;

-- формирование параметров поиска по Сущности 'jcb-4cx'
SELECT
  min(dm.digital) AS minimum,
  max(dm.digital) AS maximum
FROM

  item i
  JOIN item_content ic
    ON i.id = ic.item_id
  JOIN content c
    ON ic.content_id = c.id
  JOIN property p
    ON c.property_id = p.id
  JOIN digital_matter dm
    ON c.id = dm.content_id
WHERE
  p.code = 'GOODS_ITEM_PRICE_RUB'
  AND i.code = 'jcb-4cx';
/*
3400000
4700000
*/

SELECT
  sm.string
FROM
  item i
  JOIN item_content ic
    ON i.id = ic.item_id
  JOIN content c
    ON ic.content_id = c.id
  JOIN property p
    ON c.property_id = p.id
  JOIN string_matter sm
    ON c.id = sm.content_id
WHERE
  p.code = 'GOODS_ITEM_UNITS_OF_MEASURE'
  AND i.code = 'jcb-4cx'
GROUP BY sm.string;
/*
р/шт
шт
шт.
*/

Поисковый запрос

Видим что у всех позиций единица измерения в штуках, записана просто по разному, поэтому ищем только по цене.

/*
:ITEM_CODE => 'jcb-4cx'
:PRICE_PROPERTY => 'GOODS_ITEM_PRICE_RUB'
:MIN_PRICE => 3400000
:MAX_PRICE => 4000000
*/
SELECT
  r.id,
  r.title,
  r.description,
  c.raw
FROM
  item i
  JOIN item_content ic
    ON i.id = ic.item_id
  JOIN content c
    ON ic.content_id = c.id
  JOIN redactor_content rc
    ON c.id = rc.content_id
  JOIN redactor r
    ON rc.redactor_id = r.id
  JOIN digital_matter dm
    ON c.id = dm.content_id
  JOIN rubric_item ri
    ON i.id = ri.item_id
  JOIN rubric_property rp
    ON ri.rubric_id = rp.rubric_id
  JOIN property p
    ON c.property_id = p.id
       AND p.id = rp.property_id
WHERE
  i.code = :ITEM_CODE
  AND p.code = :PRICE_PROPERTY
  AND dm.digital BETWEEN :MIN_PRICE AND :MAX_PRICE
;
/*
Василий Алибабаевич Уримчи,бригадир СМУ-3,3 800 000
Олег,Мы всегда можем договориться о взаимовыгодном сотрудничестве,3 400 000
*/

Собственно это упрощённый поиск чисто по одной Характеристике «цена».
В результате запроса можно выдавать и digital_matter.digital, но тогда (если у нас будет INTERSECT по нескольким Характеристикам) надо делать приведение к типу TEXT ( digital_matter.digital::TEXT), в принципе пользователю мы данные выводим в текстовом виде, поэтому можно выдавать content.raw.

Заключение

Собственно этого достаточно что бы понять как можно идею идеального каталога применить на практике. Кейсов конечно на порядок больше чем поиск товара и поставщика.

Самое главное что я хотел показать, это то что идея достаточно гибкая что бы быть идеально Универсальной :)

Учитывая мой recovery_mode, ещё через неделю я смогу выложить php скрипты для динамической генерации SQL запросов.

Спасибо всем кто читал, буду благодарен за любую критику и любые советы.

Addon

Для полноты картины не хватает иерархических скриптов, для вывода всех рубрик от корневой, и для вывода всех родителей для произвольного элемента. Эти скрипты лежат в \deploy\view_catalog_settings_and_data.sql.

Карта рубрик:

-- показать карту иерархии Сущностей с уровнем иерархии, для корневой рубрики 'GOODS'
WITH RECURSIVE road_map ( id, element_tree_id, code, horizont ) AS
(
  SELECT
    pet.id              AS id,
    pet.element_tree_id AS element_tree_id,
    r.code              AS code,
    0                   AS horizont
  FROM
    element_tree pet
    LEFT JOIN rubric_element_tree ret
      ON pet.id = ret.element_tree_id
    LEFT JOIN rubric r
      ON ret.rubric_id = r.id
  WHERE r.code = :ROOT
  UNION
  SELECT
    cet.id,
    cet.element_tree_id,
    r.code,
    horizont + 1
  FROM
    element_tree cet
    JOIN road_map c
      ON (c.id = cet.element_tree_id)
    LEFT JOIN rubric_element_tree ret
      ON cet.id = ret.element_tree_id
    LEFT JOIN rubric r
      ON ret.rubric_id = r.id
)
SELECT
  code,
  horizont
FROM
  road_map
ORDER BY
  horizont ASC;

Путь от корня до заданного узла (рубрики)

-- показать путь от потомка до родительского корневого элемента 'ekskavatory-karernye'
WITH RECURSIVE road_map ( id, element_tree_id, code, horizont ) AS
(
  SELECT
    cet.id              AS id,
    cet.element_tree_id AS element_tree_id,
    r.code              AS code,
    0                   AS horizont
  FROM
    element_tree cet
    LEFT JOIN rubric_element_tree ret
      ON cet.id = ret.element_tree_id
    LEFT JOIN rubric r
      ON ret.rubric_id = r.id
  WHERE r.code = :CHILD
  UNION
  SELECT
    pet.id,
    pet.element_tree_id,
    r.code,
    horizont + 1
  FROM
    element_tree pet
    JOIN road_map c
      ON (c.element_tree_id = pet.id)
    LEFT JOIN rubric_element_tree ret
      ON pet.id = ret.element_tree_id
    LEFT JOIN rubric r
      ON ret.rubric_id = r.id
)
SELECT
  code,
  horizont
FROM
  road_map
ORDER BY
  horizont DESC;

Показать все Значения всех Характеристик одной Сущности

-- Значения Характеристик одной Сущности 'doneks-eo-4112a-1'
SELECT
  i.title,
  p.title,
  dm.digital::TEXT
FROM
  rubric_item ri
  JOIN item i
    ON ri.item_id = i.id
  JOIN item_content ic
    ON i.id = ic.item_id
  JOIN content c
    ON ic.content_id = c.id
  JOIN digital_matter dm
    ON c.id = dm.content_id
  JOIN rubric_property rp
    ON ri.rubric_id = rp.rubric_id
  JOIN property p
    ON c.property_id = p.id
       AND p.id = rp.property_id
WHERE
  i.code = :CODE
UNION
SELECT
  i.title,
  p.title,
  sm.string::TEXT
FROM
  rubric_item ri
  JOIN item i
    ON ri.item_id = i.id
  JOIN item_content ic
    ON i.id = ic.item_id
  JOIN content c
    ON ic.content_id = c.id
  JOIN string_matter sm
    ON c.id = sm.content_id
  JOIN rubric_property rp
    ON ri.rubric_id = rp.rubric_id
  JOIN property p
    ON c.property_id = p.id
       AND p.id = rp.property_id
WHERE
  i.code = :CODE;