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.

Repository content


The repository has a full set of infrastructure scripts:

  1. table creation
  2. filling data;
  3. cleaning tables from data;
  4. 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:

  1. create Rubrics;
  2. if a hierarchy of rubrics is needed, then create a hierarchy (element_tree) and distribute rubrics in a hierarchy (rubric_element_tree);
  3. add Characteristics (property);
  4. 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);
  5. assign to Rubrics Characteristics (rubric_property);
  6. add Entities (item);
  7. group Entities by Categories (rubric_item);
  8. add Values ​​(content) for Characteristics;
  9. dock Values ​​with Entities (item_content);
  10. if it is assumed that the Values ​​will have several editors, then add the Editors (redactor) and assign the Editors Values ​​(redactor_content);
  11. 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:

  1. add Entity;
  2. 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:

  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

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;

Also popular now: