Ideal catalog, implementation option
In the continuation of the article “ Ideal catalog, architecture sketch ”, I will show by examples how you can use the proposed database structure to store arbitrary data and perform arbitrary searches on this data. The scripts are in the repository - universal_data_catalog_idea .
I invite you to cat, those who are interested in looking at these scripts with author's comments.
The repository has a full set of infrastructure scripts:
When I started writing scripts, their volume was small, but when I got to the creation of test suites, the amount of code grew to 1000+ lines. It seems unnecessary to insert such large scripts into the article, so if you want to touch the “live” data, then clone the repository and see how it turns out in life.
In the DB schema, I made small changes - I took the editors (redactor_id) out of the content, now the content itself, the editors by themselves.
The system itself does not dictate the rules of use, any logic can be applied to it.
The main purpose of the system is data search, data storage is a necessary, but nonetheless secondary functionality.
You can store any data, any configuration. Catalog data consists of Entities and their values. Each Value is the value of a particular Property. Entities with the same set of Characteristics can be combined in rubric.
An entity can belong to one Rubric, it can belong to several, in the presented version only one.
How to add data to the directory can be found in \ deploy \ commit_dml.sql. The sequence is as follows:
The list came out long, but in fact all the steps fit into two actions:
The remaining steps are necessary. With the theory of information storage sorted out. Now practical application.
Suppose we want to make our Avito for the sale of excavators.
To do this, we will add the root column “Excavators”, and to it two subsidiary “Excavators quarry” and “Excavators loaders”.
For the section "Mining Excavators" assign properties:
"Product Model";
"Trademark";
"Bucket capacity";
“Price of goods in rubles”;
“Units of measurement for heading”;
For the section “Backhoe loaders” we assign a similar set of properties plus the property “Shovel capacity”.
Product specifications:
“Product Model”,
“Trademark”,
“Bucket Capacity”,
“ Shovel Capacity”,
are system, the values of these Characteristics are determined by the content manager, we assign them the Option - “SYSTEM_PROPERTY”.
“Bucket capacity” and “Shovel capacity” are numerical data - Option “DIGITAL_DATA_TYPE”, a search in them will be a search in the range of values - “BETWEEN_SEARCH_TYPE”.
“Product model” and “Trademark” are string data - “STRING_DATA_TYPE”, a search for the “Trademark” characteristic will be like a manufacturer’s reference - a listing - “ENUMERATION_SEARCH_TYPE”, by a characteristic “Product model” we will search for the substring - “LIKE_SEARCH_TYPE” .
We kill Entities and Values. Now you can perform a general search.
A general search implies a search everywhere, ours everywhere is limited only by Categories and Entities, in principle, you can also search in the Values, since they store user input in string format. Searching everywhere involves searching for a substring, in which columns? Apparently only "title" and "description".
Go!
The root rubric code (rubric.code) serves to limit the search area, we can search in the “Excavators” in general, but we can only search in the quarry ones, or we can search in the goods in general, but we can only in the excavators.
The determination of whether the next object in the ancestors has the specified Rubric occurs through a hierarchical query specific to each DBMS.
You can search without restriction by region, then if we have added services (for example, for renting excavators) and a resume (for example, an excavator driver) in our catalog and are looking for the substring “excavator”, then we can also find out the positions for renting excavators and the positions for the driver excavator.
Search string - actually the substring we are looking for, you can split the search string by spaces (or any other separator) and search with the condition:
The resulting script certainly looks "gigantic", but it is not written manually, our application writes it, and the DBMS will swallow any script, so you do not need to look at the number of letters - this is not essential.
Suppose, with the help of this search, the user has found the heading he needs, now in the heading you need to find an interesting position, for this you need to search in the Heading among Entities for the given parameters.
In order for the user to imagine the boundaries of the search, he needs to calculate these boundaries.
If we do a search in the section, then we show the search parameters (borders) only for the system Characteristics.
To do this, let's see what our section has, there are system characteristics and what search methods are set:
We look at what characteristics are systemic:
We look what type of search is defined for these Characteristics:
We look at the data type for these Characteristics:
If one of the three search parameters for the Characteristic is not set, then it is not possible to perform a search (join tables tu ts tt via JOIN).
We determine the properties for the heading "career excavators."
As a result, we get three Characteristics and search parameters:
Now for each Characteristic we are looking for boundaries.
For "MANUFACTURER_MODEL" - type of search by entry ("LIKE_SEARCH_TYPE") - we do not calculate the boundaries, we display a field for the user to enter a search string for this Characteristic.
The data type - “STRING_DATA_TYPE” - means we are analyzing - string_matter.string. The search method - "ENUMERATION_SEARCH_TYPE" - means doing "GROUP BY". We calculated two values - "Uralmash" and "Donex", the user displays two checkboxes .
The data type - "DIGITAL_DATA_TYPE" - means we are analyzing - digital_matter.digital. The search method - "BETWEEN_SEARCH_TYPE" - means doing MIN () and MAX (). We calculated the boundaries from 0.75 to 25, the user displays something like this:
For each Characteristic, we do a search in accordance with the conditions specified by the user and select those Entities that satisfy all the conditions, that is, we make the intersection - INTERSECT - of the results of each individual search with each other.
If the user sets one condition, then the search will be on one Characteristic, if 100 conditions, then 100 subqueries will be executed and those Entities that are present in the results of each of the 100 subqueries will be issued to the user.
As a result of a search by parameters, the user determined an interesting position for him (Entity).
It should be noted that a good search had to be performed with the selection by user "SYSTEM", but I missed this point.
I remind you that we are doing Avito for excavators. That is, each catalog item has its own price, and each user has his own price. And when our user opened the catalog position card, he saw several offers and, accordingly, wanted to search for these offers.
We had properties:
These are user-defined properties, their values are set by users, this is regulated by the USER_PROPERTY option.
We define a set of custom Characteristics for the section "Excavators loaders":
GOODS_ITEM_PRICE_RUB:
GOODS_ITEM_UNITS_OF_MEASURE:
We see that at all positions the unit of measurement in pieces is simply recorded in different ways, so we look only at the price.
Actually this is a simplified search purely for one characteristic "price".
As a result of the request, you can also issue digital_matter.digital, but then (if we have an INTERSECT for several Characteristics) we need to cast to TEXT type (digital_matter.digital::TEXT), in principle, we display the data in text form to the user, so we can issue content.raw.
Actually this is enough to understand how the idea of an ideal catalog can be put into practice. Cases of course an order of magnitude more than the search for goods and suppliers.
The most important thing I wanted to show is that the idea is flexible enough to be perfectly Universal :)
Considering my recovery_mode, in a week I will be able to lay out php scripts for the dynamic generation of SQL queries.
Thanks to everyone who read, I will be grateful for any criticism and any advice.
For completeness, there are not enough hierarchical scripts to display all rubrics from the root, and to display all parents for an arbitrary element. These scripts lie in \ deploy \ view_catalog_settings_and_data.sql.
Map of categories:
The path from the root to the given node (headings)
Show all Values of all Characteristics of one Entity
I invite you to cat, those who are interested in looking at these scripts with author's comments.
Repository content
The repository has a full set of infrastructure scripts:
- table creation
- filling data;
- cleaning tables from data;
- Delete all created tables
When I started writing scripts, their volume was small, but when I got to the creation of test suites, the amount of code grew to 1000+ lines. It seems unnecessary to insert such large scripts into the article, so if you want to touch the “live” data, then clone the repository and see how it turns out in life.
In the DB schema, I made small changes - I took the editors (redactor_id) out of the content, now the content itself, the editors by themselves.
Data Organization Highlights
The system itself does not dictate the rules of use, any logic can be applied to it.
The main purpose of the system is data search, data storage is a necessary, but nonetheless secondary functionality.
You can store any data, any configuration. Catalog data consists of Entities and their values. Each Value is the value of a particular Property. Entities with the same set of Characteristics can be combined in rubric.
An entity can belong to one Rubric, it can belong to several, in the presented version only one.
Adding data (storage)
How to add data to the directory can be found in \ deploy \ commit_dml.sql. The sequence is as follows:
- create Rubrics;
- if a hierarchy of rubrics is needed, then create a hierarchy (element_tree) and distribute rubrics in a hierarchy (rubric_element_tree);
- add Characteristics (property);
- if you intend to use the Characteristics in accordance with any rules, then you can add Options for these rules (tag) and appropriately match the Characteristics and Options (property_tag);
- assign to Rubrics Characteristics (rubric_property);
- add Entities (item);
- group Entities by Categories (rubric_item);
- add Values (content) for Characteristics;
- dock Values with Entities (item_content);
- if it is assumed that the Values will have several editors, then add the Editors (redactor) and assign the Editors Values (redactor_content);
- if not only string search is supposed, then convert user input (content.raw) to a specific data type and write data to the corresponding table (date_matter.date_time, digital_matter.digital, duration_matter.duration, string_matter.string);
The list came out long, but in fact all the steps fit into two actions:
- add Entity;
- set values;
The remaining steps are necessary. With the theory of information storage sorted out. Now practical application.
Practical content of the catalog
Suppose we want to make our Avito for the sale of excavators.
To do this, we will add the root column “Excavators”, and to it two subsidiary “Excavators quarry” and “Excavators loaders”.
For the section "Mining Excavators" assign properties:
"Product Model";
"Trademark";
"Bucket capacity";
“Price of goods in rubles”;
“Units of measurement for heading”;
For the section “Backhoe loaders” we assign a similar set of properties plus the property “Shovel capacity”.
Product specifications:
“Product Model”,
“Trademark”,
“Bucket Capacity”,
“ Shovel Capacity”,
are system, the values of these Characteristics are determined by the content manager, we assign them the Option - “SYSTEM_PROPERTY”.
“Bucket capacity” and “Shovel capacity” are numerical data - Option “DIGITAL_DATA_TYPE”, a search in them will be a search in the range of values - “BETWEEN_SEARCH_TYPE”.
“Product model” and “Trademark” are string data - “STRING_DATA_TYPE”, a search for the “Trademark” characteristic will be like a manufacturer’s reference - a listing - “ENUMERATION_SEARCH_TYPE”, by a characteristic “Product model” we will search for the substring - “LIKE_SEARCH_TYPE” .
We kill Entities and Values. Now you can perform a general search.
General search
A general search implies a search everywhere, ours everywhere is limited only by Categories and Entities, in principle, you can also search in the Values, since they store user input in string format. Searching everywhere involves searching for a substring, in which columns? Apparently only "title" and "description".
Go!
-- Поиск "везде" ( в рубриках и в позициях каталога )
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
);
Request Parameters
Root section
:CATALOG_ROOT
The root rubric code (rubric.code) serves to limit the search area, we can search in the “Excavators” in general, but we can only search in the quarry ones, or we can search in the goods in general, but we can only in the excavators.
The determination of whether the next object in the ancestors has the specified Rubric occurs through a hierarchical query specific to each DBMS.
You can search without restriction by region, then if we have added services (for example, for renting excavators) and a resume (for example, an excavator driver) in our catalog and are looking for the substring “excavator”, then we can also find out the positions for renting excavators and the positions for the driver excavator.
Search line
:SEARCH_PATTERN
Search string - actually the substring we are looking for, you can split the search string by spaces (or any other separator) and search with the condition:
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|| '%')
The resulting script certainly looks "gigantic", but it is not written manually, our application writes it, and the DBMS will swallow any script, so you do not need to look at the number of letters - this is not essential.
Suppose, with the help of this search, the user has found the heading he needs, now in the heading you need to find an interesting position, for this you need to search in the Heading among Entities for the given parameters.
Search by parameters
In order for the user to imagine the boundaries of the search, he needs to calculate these boundaries.
If we do a search in the section, then we show the search parameters (borders) only for the system Characteristics.
To do this, let's see what our section has, there are system characteristics and what search methods are set:
-- Для поиска по рубрике необходимо сформировать параметры поиска - диапазоны допустимых значений для системных свойств
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
*/
Explanation of the request
We look at what characteristics are systemic:
tu.code = 'SYSTEM_PROPERTY'
We look what type of search is defined for these Characteristics:
ts.code IN ('LIKE_SEARCH_TYPE','BETWEEN_SEARCH_TYPE','ENUMERATION_SEARCH_TYPE')
We look at the data type for these Characteristics:
tt.code IN ('DIGITAL_DATA_TYPE','STRING_DATA_TYPE')
If one of the three search parameters for the Characteristic is not set, then it is not possible to perform a search (join tables tu ts tt via JOIN).
We determine the properties for the heading "career excavators."
r.code = 'ekskavatory-karernye'
Query result
As a result, we get three Characteristics and search parameters:
- 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
Now for each Characteristic we are looking for boundaries.
Calculating Search Bounds
Calculating Search Bounds for "MANUFACTURER_MODEL"
For "MANUFACTURER_MODEL" - type of search by entry ("LIKE_SEARCH_TYPE") - we do not calculate the boundaries, we display a field for the user to enter a search string for this Characteristic.
Calculating Search Boundaries for 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;
/*
Уралмаш
Донэкс
*/
The data type - “STRING_DATA_TYPE” - means we are analyzing - string_matter.string. The search method - "ENUMERATION_SEARCH_TYPE" - means doing "GROUP BY". We calculated two values - "Uralmash" and "Donex", the user displays two checkboxes .
Calculating Search Bounds for "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
*/
The data type - "DIGITAL_DATA_TYPE" - means we are analyzing - digital_matter.digital. The search method - "BETWEEN_SEARCH_TYPE" - means doing MIN () and MAX (). We calculated the boundaries from 0.75 to 25, the user displays something like this:
Search by parameters
-- поиск по рубрике
/*
'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
;
For each Characteristic, we do a search in accordance with the conditions specified by the user and select those Entities that satisfy all the conditions, that is, we make the intersection - INTERSECT - of the results of each individual search with each other.
If the user sets one condition, then the search will be on one Characteristic, if 100 conditions, then 100 subqueries will be executed and those Entities that are present in the results of each of the 100 subqueries will be issued to the user.
As a result of a search by parameters, the user determined an interesting position for him (Entity).
It should be noted that a good search had to be performed with the selection by user "SYSTEM", but I missed this point.
Custom Value Search
I remind you that we are doing Avito for excavators. That is, each catalog item has its own price, and each user has his own price. And when our user opened the catalog position card, he saw several offers and, accordingly, wanted to search for these offers.
We had properties:
- “Price of goods in rubles”;
- “Units of measurement for heading”;
These are user-defined properties, their values are set by users, this is regulated by the USER_PROPERTY option.
Search Borders
We define a set of custom Characteristics for the section "Excavators loaders":
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" - search by range;
- "DIGITAL_DATA_TYPE" - numeric data;
GOODS_ITEM_UNITS_OF_MEASURE:
- "ENUMERATION_SEARCH_TYPE" - listing with enumeration;
- "STRING_DATA_TYPE" - character data;
-- формирование параметров поиска по Сущности '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;
/*
р/шт
шт
шт.
*/
Search query
We see that at all positions the unit of measurement in pieces is simply recorded in different ways, so we look only at the price.
/*
: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
*/
Actually this is a simplified search purely for one characteristic "price".
As a result of the request, you can also issue digital_matter.digital, but then (if we have an INTERSECT for several Characteristics) we need to cast to TEXT type (digital_matter.digital::TEXT), in principle, we display the data in text form to the user, so we can issue content.raw.
Conclusion
Actually this is enough to understand how the idea of an ideal catalog can be put into practice. Cases of course an order of magnitude more than the search for goods and suppliers.
The most important thing I wanted to show is that the idea is flexible enough to be perfectly Universal :)
Considering my recovery_mode, in a week I will be able to lay out php scripts for the dynamic generation of SQL queries.
Thanks to everyone who read, I will be grateful for any criticism and any advice.
Addon
For completeness, there are not enough hierarchical scripts to display all rubrics from the root, and to display all parents for an arbitrary element. These scripts lie in \ deploy \ view_catalog_settings_and_data.sql.
Map of categories:
-- показать карту иерархии Сущностей с уровнем иерархии, для корневой рубрики '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;
The path from the root to the given node (headings)
-- показать путь от потомка до родительского корневого элемента '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;
Show all Values of all Characteristics of one Entity
-- Значения Характеристик одной Сущности '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;