Идеальный каталог, вариант реализации

В продолжении статьи "Идеальный каталог, набросок архитектуры", я покажу на примерах как можно использовать предложенную структуру БД для хранения произвольных данных и выполнения произвольных поисков по этим данным. Скрипты лежат в репозитории — 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;

Similar posts

AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 22

    +2
    Для полноты картины не хватает иерархических скриптов


    Для полноты картины не хватает експлейнов этих запросов.
    Зы. заюзать ES имхо проще.
      0
      эксплэйны зависят от статистики запросов к таблицам и статистики использования индексов и прочих статистик, на тестовой базе из 10-ти записей и с нулём пользователей, толку от таких эксплейнов ноль.

      ЗЫ
      ES — это что?
        0
        эксплэйны зависят от статистики запросов к таблицам и статистики использования индексов и прочих статистик, на тестовой базе из 10-ти записей и с нулём пользователей, толку от таких эксплейнов ноль.

        Ну так как я не пользуюсь данной схемой и у меня нету готовых данных под эту струтуру я сам их посмотреть не могу. Вот и написал, что не помешали бы експлейны от автора. Он то ведь наверняка юзает.

        зы
        Elastic Search.

        ззы. Есть и аналоги, конечно
          0
          автор предложил, но команда отказалась с таким работать :) поэтому автор с чистой совестью делиться забракованной идей.

          по Elastic Search надо в деле попробовать, оно по иерархии умеет искать? в качестве поискового движка наверное можно использовать, но у архитектуры есть ещё и переброска туда сюда, того сего :))) была позиция экскаватором погрузчиком, а стала экскаватором карьерным и всякое такое прочее, типа вычисление средней цены и прочей аналитики.

          вообще это вторая часть «повествования», первая была про архитектуру, а как реализовать — PG / MySql или ES дело десятое, просто пример реализации.
            0
            А почему команда отказалась то?

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

              Но в тоже время одна из причин публикации в том что бы послушать критику. Всё что было сказано в коментах к первой части свелось только к тому что иерархические запросы это плохо, собственно архитектуру ни кто не «осудил».
              Вот сейчас мне рассказали про ES — уже что то, хотя если оно ищет в рамках только одной таблицы, то не годиться, короче надо посмотреть на практике что оно умеет.
                0
                простейший фильтр по свойствам товаров типа умеет, осталось тесты провести что быстрее, и что удобней в отладке и профилировании.
                  0
                  В ES нет таблиц. Есть индексы и документы. Похоже на коллекции в монге. Умеет быстро искать по любой комбинации полей документа. Умеет ранжирование по важности поля. Умеет в пагинацию. Что вы положите в документ — дело сугубо ваше
                    0
                    звучит заманчиво
        +2
        Если бы мне предложили разрабатывать каталог на основе такой схемы БД, я бы тоже отказался — достаточно представить как весело будет делать ORM под нее. Излишняя универсальность часто приводит к чрезмерной сложности.
          0
          вам кажется, даже когда пишешь запросы в DataGrip нет ни каких проблем с джоинами, пишешь «join item_content ic on » и тут DataGrip сам подставляет нужные поля «ON i.id = ic.item_id» — только успевай что контрол пробел нажимать, а уж всякие Yii2 с пол пинка генерят:

          backend/models/BusinessObject.php
              public function getBusinessProcesses()
              {
                  return $this->hasMany(BusinessProcess::className(), ['id' => 'business_process_id'])->viaTable('privilege', ['business_object_id' => 'id']);
              }
          


          то есть Gii видит, что business_process связан с business_object через privilege, и в методах можно использовать getBusinessProcesses и даже не знать что оно связано через privilege.

          Мой жизненный опыт говорит мне: всё сложности у человека в голове, в жизни всё решаемо.
          +1

          Коллега SbWereWolf, впечатлен поисковым SQL'ом в полной мере. Универсальность подразумевает сложность. Вернее даже многоступенчатую связность причин-следствий. "Почему здесь Ж? Вот смотрите: из А следует Б, из Б — В,… а из Ё — Ж!" Принцип "7 плюс-минус 2" описывает кол-во объектов, на которых одновременно может фокусироваться человек. Популярные решения ориентированы на тех, кто "5". А "Ж" — это уже 8. Еще подъемно, но уже не всем. Мне, например, тяжело понять, как работает "общепоисковой SQL", но я восхищен вашей способностью его составить.

            0
            промежуточные таблицы учитывать не надо, рабочих таблиц там пять, это если по феншую делать, если не по феншую то 4.

            Зачем в голове держать промежуточные? их название очевидны, если я хочу связать item и content, то я использую item_content — это просто дополнительная строчка в запросе.

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


            максимум сюда закидывается иерархия, но всё это фигня, один раз пишем слой доступа к данным и забываем его как страшный сон.
            Всякое добавление записей — аналогично, два дня слёз и боли и можно об этом больше не вспоминать и говорить уже на языке бизес логики, потому что когда алфавит и словарь разработаны, уж предложения из них складывать это уже фигня делов.

            и как я говорю весь SQL генериться приложением на лету, наше дело написать методы для генерации элементарных кусочков, а они очень простые, реально простые :))

            ещё через неделю покажу.

            PS
            по поводу ES подумал, что у меня его функционал повторяется, но фишка в том что новому прогеру уже знакомому с ES будет легче понять кодовую базу, поэтому таки через какое то время надо будет с моего велосипеда пересесть на ES, хотя… мой велосипед ещё ни разу из «гаража» не выезжал :)
            0
            В одной из компаний, где я работал, использовалась собственная реализация чего-то подобного.
            В общих чертах можно описать так. Во главе всего идёт метаописание структуры данных. Структура описана в .mdb файле. В этом файле архитектором создаются сущности, свойства (простые, табличные, агрегированные и ещё несколько типов). Был написан специальный инструмент для упрощения редактирования этой метаинформации. Сущности описывались древовидной структурой с наследованием — т.е. от базового объекта были производные объекты, наследующие свойства базовых объектов и добавляющие собственные свойства.
            После этого .mdb файлик скармливается специальной софтине, которая отражает эту структуру на реальную БД, создавая хранилище.
            К этому всему написан специальный ORM-фреймворк (.Net), реализующий DAL, BE и UI слои.
            Фреймворк позволяет выполнять заполнение, редактирование списков объектов, карточек объектов, а также относительно эффективный поиск.
            В коде объект отражался на специальный класс с динамическим списком свойств и при необходимости создавались наследники от базового класса с отражением динамических свойств на конкретные геттеры/сеттеры.
            Все списки и карточки — универсальные, работающие через метаописание структуры данных. Естественно всё это дело расширяемое и при необходимости можно расширять стандартные списки/карточки для более тонкого редактирования специфических объектов со специфическими свойствами.
            В базовом фреймворке уже есть предустановленная структура объектов типа пользователей и других объектов, являющихся базовыми для любого прикладного проекта.
            На основании этого фреймворка разрабатывались прикладные проекты, расширяющие базовую структуру данных. У прикладного проекта своя собственная структура данных, которая мёржится с базовой структурой.
            Кроме того имеется классификатор, позволяющий просматривать итоговую мета-структуру данных для изучения предметной области.
            Помимо всего этого там было накручена тонна рюшечек и компания разрабатывает 5-7 прикладных проектов на базе этого фреймворка.
            По поводу производительности всё довольно неплохо, среди прикладных проектов — развитые системы для крупного ритейла, складской софт с адресным хранением, логистика и т.д.
            Всех деталей сразу не припомнить и не описать, вся эта штука разрабатывается уже даже не десяток лет но в целом имеет свой шарм, хотя и имеет высокий порог входа — настолько много кода там налопачено.
              0
              запилить что то типа того моя мечта :))
                0
                Реализация сего чуда заняла что-то около года труда 5-10 разрабов, а доведение этой поделки до ума продолжается и по сей день. Если без фанатизма и преждевременного перенасыщения продукта фичами — в принципе может получиться неплохая расширяемая платформа, но в целом нужно иметь конкретную сферу применения в виде развитой линейки прикладных продуктов, иначе овчинка выделки не стоит.
                  0
                  если фирма собирается клепать софт, то какая то платформа или набор болванок-модулей ей всё равно пригодиться.
              0
              Вы не смотрели на RDF-хранилища и язык запросов SPARQL?
              Эта технология для каталогов подходит лучше, чем реляционные базы данных.
                0
                спасибо за подсказку, будем почитать
                  0

                  Я даже пример набросал: https://habrahabr.ru/post/324012/ :-)

                    0
                    почитал, без понятия как с OrientDB работать, но это наверное повод научиться?
                    спасибо.
                      0

                      Конечно, на графах все эти задачи решаются куда проще.

              Only users with full accounts can post comments. Log in, please.