В продолжении статьи "Идеальный каталог, набросок архитектуры", я покажу на примерах как можно использовать предложенную структуру БД для хранения произвольных данных и выполнения произвольных поисков по этим данным. Скрипты лежат в репозитории — universal_data_catalog_idea.
Приглашаю под кат, тех кому интересно посмотреть на эти скрипты с авторскими комментариями.
В репозитории полный набор инфраструктурных скриптов:
Когда я начинал писать скрипты их объём был небольшим, но когда я добрался до создания тестовых наборов, объём кода вырос до 1000+ строк. Такие большие скрипты вставлять в статью мне кажется излишним, поэтому если вам хочется потрогать «живые» данные, то клонируйте репозиторий и смотрите как оно в жизни получается.
В схеме БД я сделал небольшие изменения — редакторов (redactor_id) вынес из контента (content), теперь контент сам по себе, редакторы сами по себе.
Сама по себе система не диктует правил использования, логика к ней может быть применена любая.
Основное назначение системы это поиск данных, хранение данных — это необходимый, но тем не менее второстепенный функционал.
Хранить можно любые данные, любой конфигурации. Данные каталога состоят из Сущностей (item) и их Значений (content). Каждое Значение, является значением конкретной Характеристики (property). Сущности с одним и тем же набором Характеристик можно объединить в Рубрики (rubric).
Сущность может принадлежать одной Рубрике, может — нескольким, в представленном варианте — только одной.
Как добавлять данные в каталог можно посмотреть в \deploy\commit_dml.sql. Последовательность такая:
Список вышел длинным, но по факту все шаги укладываются в два действия:
Остальные шаги по необходимости. С теорией хранения информации разобрались. Теперь практическое применение.
Допустим мы хотим сделать своё «авито» для продажи экскаваторов.
Для этого мы добавим корневую рубрику «Экскаваторы», и к ней две дочерних «Экскаваторы карьерные» и «Экскаваторы погрузчики».
Для рубрики «Экскаваторы карьерные» назначаем свойства:
«Модель изделия»;
«Торговая марка»;
«Ёмкость ковша»;
«цена товара в рублях»;
«единицы измерения для товарной позиции»;
Для рубрики «Экскаваторы погрузчики» назначаем аналогичный набор свойств плюс свойство «Ёмкость лопаты».
Характеристики:
«Модель изделия»,
«Торговая марка»,
«Ёмкость ковша»,
«Ёмкость лопаты»,
являются системными, значения этих Характеристик задаёт контент менеджер, назначаем им Опцию — «SYSTEM_PROPERTY».
«Ёмкость ковша» и «Ёмкость лопаты» это числовые данные — Опция «DIGITAL_DATA_TYPE», поиск по ним будет поиском по диапазону значений — «BETWEEN_SEARCH_TYPE».
«Модель изделия» и «Торговая марка» это строковые данные — «STRING_DATA_TYPE», поиск по Характеристике «Торговая марка» будет как по справочнику производителей — перечислением — «ENUMERATION_SEARCH_TYPE», по Характеристике «Модель изделия» будем искать вхождение подстроки — «LIKE_SEARCH_TYPE».
Забиваем Сущности и Значения. Теперь можно выполнить общий поиск.
Общий поиск подразумевает под собой поиск везде, наше везде ограничено только Рубриками и Сущностями, в принципе можно искать ещё и в Значениях, благо они хранят пользовательский ввод в формате строк. Поиск везде подразумевает поиск подстроки, в каких колонках? Видимо только «title» и «description».
Поехали!
Код корневой Рубрики (rubric.code), служит для ограничения области поиска, можем искать в «Экскаваторах» вообще, а можем искать только в карьерных, или можем искать в товарах вообще, а можем только в экскаваторах.
Определение, имеет ли очередной объект в предках заданную Рубрику, происходит через иерархический запрос специфичный для каждой СУБД.
Можно искать без ограничения по области, тогда если мы добавили в наш каталог услуги (например по аренде экскаваторов) и резюме (например машиниста экскаватора) и ищем подстроку «экскаватор», то у нас в результаты вываляться и позиции по аренде экскаваторов и позиции про машиниста экскаватора.
Строка поиска — собственно та подстрока которую будем искать, можно строку поиска разбить по пробелам (или любому другому разделителю) и искать с условием:
Получившийся скрипт конечно выглядит «гигантским», но пишется он не в ручную, пишет его наше приложение, а СУБД проглотит любой скрипт, поэтому не надо смотреть на количество букв — это не существенно.
Допустим с помощью этого поиска пользователь нашёл нужную ему рубрику, теперь в рубрике надо найти интересную позицию, для этого надо в Рубрике сделать поиск среди Сущностей по заданным параметрам.
Что бы пользователь представлял себе границы поиска, для него надо эти границы вычислить.
Если мы делаем поиск по рубрике, то показываем параметры (границы) поиска только для системных Характеристик.
Для этого посмотрим какие у нашей рубрики, есть системные Характеристики и какие способы поиска заданы:
Смотрим какие Характеристик являются системными:
Смотрим какой тип поиска определён для этих Характеристик:
Смотрим тип данных для этих Характеристик:
Если один из трех параметров поиска для Характеристики не задан, то поиск выполнить не возможно (соединение таблиц tu ts tt через JOIN).
Определяем свойства для рубрики «Экскаваторы карьерные».
В итоге получаем три Характеристики и параметры поиска:
Теперь для каждой Характеристики ищем границы.
Для «MANUFACTURER_MODEL» — тип поиска по вхождению («LIKE_SEARCH_TYPE») — границы не вычисляем, пользователю выводим поле для ввода строки поиска по этой Характеристике.
Тип данных — «STRING_DATA_TYPE» — значит анализируем — string_matter.string. Способ поиска — «ENUMERATION_SEARCH_TYPE» — значит делаем «GROUP BY». Вычислили два значения — «Уралмаш» и «Донэкс», пользователю выводим два чекбокса.
Тип данных — «DIGITAL_DATA_TYPE» — значит анализируем — digital_matter.digital. Способ поиска — «BETWEEN_SEARCH_TYPE» — значит делаем MIN() и MAX(). Вычислили границы от 0.75 до 25, пользователю выводим что то такое:
По каждой Характеристике делаем поиск в соответствии с условиями заданными пользователем и выбираем те Сущности которые удовлетворяют всем условиям, то есть делаем пересечение — INTERSECT — результатов каждого отдельного поиска друг с другом.
Если пользователь задаст одно условие то поиск будет по одной Характеристике, если 100 условий, то будет выполнено 100 подзапросов и пользователю будут выданы те Сущности которые присутствуют в результатах каждого из 100 подзапросов.
В результате поиска по параметрам пользователь определил интересную ему позицию (Сущность).
Надо заметить что по хорошему поиск надо было выполнять с отбором по пользователю «SYSTEM», но я этот момент упустил.
Напоминаю что мы делаем «авито» для экскаваторов. То есть каждая позиция каталога имеет свою цену, и у каждого пользователя своя цена. И когда наш пользователь открыл карточку позиции каталога он увидел несколько предложений и соответственно захотел выполнить поиск по этим предложениям.
Были у нас свойства:
Это пользовательские свойства, их значения задают пользователи, регулируется это Опцией «USER_PROPERTY».
Определяем набор пользовательских Характеристик для рубрики «Экскаваторы погрузчики»:
«GOODS_ITEM_PRICE_RUB»:
«GOODS_ITEM_UNITS_OF_MEASURE»:
Видим что у всех позиций единица измерения в штуках, записана просто по разному, поэтому ищем только по цене.
Собственно это упрощённый поиск чисто по одной Характеристике «цена».
В результате запроса можно выдавать и digital_matter.digital, но тогда (если у нас будет INTERSECT по нескольким Характеристикам) надо делать приведение к типу TEXT ( digital_matter.digital::TEXT), в принципе пользователю мы данные выводим в текстовом виде, поэтому можно выдавать content.raw.
Собственно этого достаточно что бы понять как можно идею идеального каталога применить на практике. Кейсов конечно на порядок больше чем поиск товара и поставщика.
Самое главное что я хотел показать, это то что идея достаточно гибкая что бы быть идеально Универсальной :)
Учитывая мой recovery_mode, ещё через неделю я смогу выложить php скрипты для динамической генерации SQL запросов.
Спасибо всем кто читал, буду благодарен за любую критику и любые советы.
Для полноты картины не хватает иерархических скриптов, для вывода всех рубрик от корневой, и для вывода всех родителей для произвольного элемента. Эти скрипты лежат в \deploy\view_catalog_settings_and_data.sql.
Карта рубрик:
Путь от корня до заданного узла (рубрики)
Показать все Значения всех Характеристик одной Сущности
Приглашаю под кат, тех кому интересно посмотреть на эти скрипты с авторскими комментариями.
Содержание репозитория
В репозитории полный набор инфраструктурных скриптов:
- создание таблиц
- заполнения данными;
- очистки таблиц от данных;
- удаления всех созданных таблиц;
Когда я начинал писать скрипты их объём был небольшим, но когда я добрался до создания тестовых наборов, объём кода вырос до 1000+ строк. Такие большие скрипты вставлять в статью мне кажется излишним, поэтому если вам хочется потрогать «живые» данные, то клонируйте репозиторий и смотрите как оно в жизни получается.
В схеме БД я сделал небольшие изменения — редакторов (redactor_id) вынес из контента (content), теперь контент сам по себе, редакторы сами по себе.
Основные моменты организации данных
Сама по себе система не диктует правил использования, логика к ней может быть применена любая.
Основное назначение системы это поиск данных, хранение данных — это необходимый, но тем не менее второстепенный функционал.
Хранить можно любые данные, любой конфигурации. Данные каталога состоят из Сущностей (item) и их Значений (content). Каждое Значение, является значением конкретной Характеристики (property). Сущности с одним и тем же набором Характеристик можно объединить в Рубрики (rubric).
Сущность может принадлежать одной Рубрике, может — нескольким, в представленном варианте — только одной.
Добавление данных (хранение)
Как добавлять данные в каталог можно посмотреть в \deploy\commit_dml.sql. Последовательность такая:
- создать Рубрики (rubric);
- если необходима иерархия Рубрик, то создать Иерархию (element_tree) и распределить рубрики по иерархии (rubric_element_tree);
- добавить Характеристики (property);
- если предполагается использование Характеристик в соответствии с какими либо правилами, то можно добавить Опции этих правил (tag) и соответствующим образом стыковать Характеристики и Опции (property_tag);
- назначить Рубрикам Характеристики (rubric_property);
- добавить Сущности (item);
- сгруппировать Сущности по Рубрикам (rubric_item);
- добавить Значения (content) для Характеристик;
- стыковать Значения с Сущностями (item_content);
- если предполагается что у Значений будет несколько редакторов, то добавить Редакторов (redactor) и назначить Значениям Редакторов (redactor_content);
- если предполагается не только строковый поиск, то конвертировать пользовательский ввод (content.raw) в конкретный тип данных и записать данные в соответствующую таблицу (date_matter.date_time, digital_matter.digital, duration_matter.duration, string_matter.string);
Список вышел длинным, но по факту все шаги укладываются в два действия:
- добавить Сущность;
- задать Значения;
Остальные шаги по необходимости. С теорией хранения информации разобрались. Теперь практическое применение.
Практическое наполнение каталога
Допустим мы хотим сделать своё «авито» для продажи экскаваторов.
Для этого мы добавим корневую рубрику «Экскаваторы», и к ней две дочерних «Экскаваторы карьерные» и «Экскаваторы погрузчики».
Для рубрики «Экскаваторы карьерные» назначаем свойства:
«Модель изделия»;
«Торговая марка»;
«Ёмкость ковша»;
«цена товара в рублях»;
«единицы измерения для товарной позиции»;
Для рубрики «Экскаваторы погрузчики» назначаем аналогичный набор свойств плюс свойство «Ёмкость лопаты».
Характеристики:
«Модель изделия»,
«Торговая марка»,
«Ёмкость ковша»,
«Ёмкость лопаты»,
являются системными, значения этих Характеристик задаёт контент менеджер, назначаем им Опцию — «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'
Результат запроса
В итоге получаем три Характеристики и параметры поиска:
- 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
Теперь для каждой Характеристики ищем границы.
Вычисление границ поиска
Вычисление границ поиска для «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;
