Сценарії поєднання таблиць за допомогою JOIN із додатковими умовами AND та OR

1. Загальний опис

Операція <ext:join> дозволяє поєднувати таблиці за певними умовами. Використовується при створенні критеріїв пошуку всередині тегу <ext:createSearchCondition> для отримання необхідних даних у зведених таблицях.

Є 3 основні типи поєднання таблиць за допомогою JOIN:

  • INNER JOIN — Перетин даних двох таблиць. Наприклад, <ext:join type="inner">.

  • LEFT JOIN — вивід даних з першої таблиці (зліва) та приєднання даних другої таблиці (справа), де це можливо. Наприклад, <ext:join type="left">.

  • RIGHT JOIN — протилежний до LEFT JOIN. Наприклад, <ext:join type="right">.

Операцію <ext:join> можна використовувати із додатковими умовами and та or, які визначаються в рамках тегу <ext:condition> як значення атрибута logicOperator.

2. Приклади використання

Розглянемо приклади використання умови JOIN у критеріях пошуку та додаткові умови AND та OR в рамках рольової моделі та її окремого випадку із застосуванням довідників кодифікатора КАТОТТГ.

2.1. Передумови

Створіть дві таблиці для використання у критеріях пошуку та поєднання за допомогою JOIN.

  1. <createTable tableName="katottg"> — таблиця, що містить коди КАТОТТГ.

    Приклад. Таблиця "katottg" 
    <changeSet id="table katottg" author="registry_owner">
    <createTable tableName="katottg" ext:historyFlag="true" remarks="katottg">
        <column name="katottg_id"  type="UUID" defaultValueComputed="uuid_generate_v4()"
                remarks="Ідентифікатор katottg">
            <constraints nullable="false" primaryKey="true" primaryKeyName="pk_katottg_id"/>
        </column>

        <column name="code" type="TEXT" remarks="Код">
            <constraints nullable="true"/>
        </column>
        <column name="name" type="TEXT" remarks="Назва">
            <constraints nullable="true"/>
        </column>
        <column name="category" type="TEXT" remarks="Категорія">
            <constraints nullable="true"/>
        </column>
        <column name="level" type="INT">
            <constraints nullable="false"/>
        </column>
        <column name="katottg_parent_id" type="UUID">
            <constraints nullable="true"
                         foreignKeyName="fk_katottg_parent"
                         referencedTableName="katottg"
                         referencedColumnNames="katottg_id"/>
        </column>
    </createTable>
    <addUniqueConstraint tableName="katottg" columnNames="code"/>
</changeSet>

  2. <createTable tableName="katottg_category" — таблиця, що містить категорії кодів КАТОТТГ.

    Приклад. Таблиця "katottg_category" 
    <changeSet id="table katottg_category" author="registry_owner">
    <createTable tableName="katottg_category" ext:historyFlag="true" remarks="katottg_category">
        <column name="katottg_category_id"  type="UUID" defaultValueComputed="uuid_generate_v4()"
                remarks="Ідентифікатор">
            <constraints nullable="false" primaryKey="true" primaryKeyName="pk_katottg_category_id"/>
        </column>
        <column name="code" type="TEXT" remarks="Код">
            <constraints nullable="false"/>
        </column>
        <column name="name" type="TEXT" remarks="Назва">
            <constraints nullable="true"/>
        </column>
    </createTable>
    <addUniqueConstraint tableName="katottg_category" columnNames="code"/>
</changeSet>

2.2. Сценарії

Створимо критерій пошуку get_regions_or_city_regions. На основі цього критерію пошуку буде створено ендпоінт у фабриці даних, який зможе повертати список усіх областей України, а також міста зі спеціальним статусом, їх коди КАТОТТГ, а також категорію коду КАТОТТГ, при виконанні певних умов запита, а саме:

  • Об’єднати таблиці за допомогою JOIN та додатковою умовою AND:

    <ext:condition logicOperator="and" columnName="k.category" operator="eq"  value="'K'"/>

  • Об’єднати таблиці за допомогою JOIN та додатковою умовою OR:

    <ext:condition logicOperator="or" columnName="k.category" operator="eq"  value="cat.code">
	<ext:condition logicOperator="and" columnName="k.category" operator="ne"  value="'K'"/>
	<ext:condition logicOperator="and" columnName="k.level" operator="eq"  value="'1'"/>
</ext:condition>

Залежно від умови, яку ви зазначаєте у запиті, формується й відповідний результат. Тобто формується вибірка з певної кількості записів, об’єднаних в одній зведеній таблиці.

2.2.1. Використання INNER JOIN без додаткових умов

Приклад 1. XML-схема. Використання INNER JOIN без додаткових умов
<ext:createSearchCondition name="get_regions_or_citi_regions">
	<ext:table name="katottg" alias="k">
		<ext:column name="katottg_id" />
		<ext:column name="name" alias="name_region" searchType="startsWith" />
		<ext:column name="category" />
	</ext:table>
	<ext:table name="katottg_category" alias="cat">
		<ext:column name="name" alias="name_category" />
		<ext:column name="code" />
	</ext:table>
	<ext:join type="inner">
		<ext:left alias="k">
			<ext:column name="category" />
		</ext:left>
		<ext:right alias="cat">
			<ext:column name="code" />
		</ext:right>
	</ext:join>
</ext:createSearchCondition>
На виході Liquibase генерує наступний SQL-запит:
Приклад 2. SQL-запит. Використання INNER JOIN без додаткових умов
CREATE
OR REPLACE VIEW registry.get_regions_or_citi_regions_v AS
SELECT
  k.katottg_id,
  k.name AS name_region,
  cat.name AS name_category,
  cat.code
FROM
  katottg k
  JOIN katottg_category cat
  ON k.category = cat.code

2.2.2. Використання INNER JOIN із додатковою умовою AND

Приклад 3. XML-схема. Використання INNER JOIN з умовою AND
<ext:createSearchCondition name="get_regions_or_citi_regions">
	<ext:table name="katottg" alias="k">
		<ext:column name="katottg_id" />
		<ext:column name="name" alias="name_region" searchType="startsWith" />
		<ext:column name="category" />
	</ext:table>
	<ext:table name="katottg_category" alias="cat">
		<ext:column name="name" alias="name_category" />
		<ext:column name="code" />
	</ext:table>
	<ext:join type="inner">
		<ext:left alias="k">
			<ext:column name="category" />
		</ext:left>
		<ext:right alias="cat">
			<ext:column name="code" />
		</ext:right>
		<ext:condition logicOperator="and" columnName="k.category" operator="eq"  value="'K'"/>
	</ext:join>
</ext:createSearchCondition>
На виході Liquibase генерує наступний SQL-запит:
Приклад 4. SQL-запит. Використання INNER JOIN з умовою AND
CREATE
OR REPLACE VIEW registry.get_regions_or_citi_regions_v AS
SELECT
  k.katottg_id,
  k.name AS name_region,
  cat.name AS name_category,
  cat.code
FROM
  katottg k
  JOIN katottg_category cat
  ON k.category = cat.code
  AND k.category = 'K' :: text;
Результат виконання запита буде таким:

Якщо об’єднати таблиці за допомогою JOIN із додатковою умовою AND, то ви отримаєте вибірку лише з 2-х записів:

join and or usage 1
Зображення 1. Результат запита за умовами JOIN + AND

2.2.3. Використання INNER JOIN із додатковою умовою OR

Приклад 5. Використання INNER JOIN з умовою OR
<ext:createSearchCondition name="get_regions_or_citi_regions">
	<ext:table name="katottg" alias="k">
		<ext:column name="katottg_id" />
		<ext:column name="name" alias="name_region" searchType="startsWith" />
		<ext:column name="category" />
	</ext:table>
	<ext:table name="katottg_category" alias="cat">
		<ext:column name="name" alias="name_category" />
		<ext:column name="code" />
	</ext:table>
	<ext:join type="inner">
		<ext:left alias="k">
			<ext:column name="category" />
		</ext:left>
		<ext:right alias="cat">
			<ext:column name="code" />
		</ext:right>
		<ext:condition logicOperator="or" columnName="k.category" operator="eq"  value="cat.code">
			<ext:condition logicOperator="and" columnName="k.category" operator="ne"  value="'K'"/>
			<ext:condition logicOperator="and" columnName="k.level" operator="eq"  value="'1'"/>
                </ext:condition>
	</ext:join>
</ext:createSearchCondition>
На виході Liquibase генерує наступний SQL-запит:
Приклад 6. SQL-запит. Використання INNER JOIN з умовою OR
CREATE
OR REPLACE VIEW registry.get_regions_or_citi_regions_v AS
SELECT
  k.katottg_id,
  k.name AS name_region,
  cat.name AS name_category,
  cat.code
FROM
  katottg k
  JOIN katottg_category cat
  ON k.category = cat.code
  OR k.category = cat.code AND k.category <> 'K'::text AND k.level = 1;
Результат виконання запита буде таким:

Якщо об’єднати таблиці за допомогою JOIN із додатковою умовою OR, то ви отримаєте вибірку з 25 записів:

join and or usage 2
Зображення 2. Результат запита за умовами JOIN + OR

2.2.4. Використання INNER JOIN із додатковими умовами AND та OR

Приклад 7. XML-схема. Використання INNER JOIN з умовами AND та OR
<ext:createSearchCondition name="get_regions_or_citi_regions">
	<ext:table name="katottg" alias="k">
		<ext:column name="katottg_id" />
		<ext:column name="name" alias="name_region" searchType="startsWith" />
		<ext:column name="category" />
	</ext:table>
	<ext:table name="katottg_category" alias="cat">
		<ext:column name="name" alias="name_category" />
		<ext:column name="code" />
	</ext:table>
	<ext:join type="inner">
		<ext:left alias="k">
			<ext:column name="category" />
		</ext:left>
		<ext:right alias="cat">
			<ext:column name="code" />
		</ext:right>
		<ext:condition logicOperator="and" columnName="k.category" operator="eq"  value="'K'"/>
        <ext:condition logicOperator="or" columnName="k.category" operator="eq"  value="cat.code">
			<ext:condition logicOperator="and" columnName="k.category" operator="ne"  value="'K'"/>
			<ext:condition logicOperator="and" columnName="k.level" operator="eq"  value="'1'"/>
        </ext:condition>
	</ext:join>
</ext:createSearchCondition>
На виході Liquibase генерує наступний SQL-запит:
Приклад 8. SQL-запит. Використання INNER JOIN з умовами AND та OR
CREATE
OR REPLACE VIEW registry.get_regions_or_citi_regions_v AS
SELECT
  k.katottg_id,
  k.name AS name_region,
  cat.name AS name_category,
  cat.code
FROM
  katottg k
  JOIN katottg_category cat
  ON k.category = cat.code
  AND k.category = 'K' :: text
  OR k.category = cat.code AND k.category <> 'K'::text AND k.level = 1;
Результат виконання запита буде таким:

Якщо об’єднати таблиці за допомогою JOIN із додатковими умовами AND та OR, то ви отримаєте вибірку з 27 записів:

join and or usage 3
Зображення 3. Результат запита за умовами JOIN + AND + OR

3. Інші приклади використання

Функціональне розширення liquibase дозволяє додавати довільні умови до основних умов <ext:join>.

3.1. Використання LEFT JOIN з однією додатковою умовою OR

Наприклад, для операції поєднання двох таблиць cities та katottg_dictionary, за умови що katottg дорівнює або level4 або add_level, в <ext:join> використовуються <ext:left> та <ext:right> для визначення основної умови — katottg=level4, та <ext:condition> для визначення додаткової умови — katottg=add_level та методу логічного поєднання умов — OR.

Приклад 9. XML-схема поєднання таблиць cities та katottg_dictionary з однією додатковою умовою OR
<changeSet author="registry owner" id="or join">
    <ext:createSearchCondition name="cities_4_or_5_level">
        <ext:table name="cities" alias="ci">
            <ext:column name="city_id" />
            <ext:column name="name" searchType="startsWith" />
            <ext:column name="katottg" searchType="equal" />
        </ext:table>
        <ext:table name="katottg_dictionary" alias="kd">
            <ext:column name="name" alias="name_dict" />
        </ext:table>
        <ext:join type="left">
            <ext:left alias="ci">
                <ext:column name="katottg" />
            </ext:left>
            <ext:right alias="kd">
                <ext:column name="level4" />
            </ext:right>
            <ext:condition logicOperator="or" columnName="ci.katottg" operator="eq" value="kd.add_level" />
        </ext:join>
    </ext:createSearchCondition>
</changeSet>
На виході Liquibase генерує наступний SQL-запит:
Приклад 10. SQL-запит. Використання LEFT JOIN з умовою OR
SELECT ci.city_id, ci.name, ci.katottg, kd.name AS name_dict
  FROM cities AS ci LEFT JOIN katottg_dictionary AS kd
    ON (ci.katottg = kd.level4) or (ci.katottg = kd.add_level);

3.2. Використання LEFT JOIN з декількома додатковими умовами AND та OR

Також за допомогою <ext:condition> можна моделювати складніші умови поєднання таблиць.

Наприклад, для тих самих таблиць cities та katottg_dictionary, якщо умова з’єднання — katottg дорівнює level4, коли category не дорівнює 'B', або katottg дорівнює add_level, коли category дорівнює 'B', то схема критерію пошуку виглядатиме наступним чином:

Приклад 11. XML-схема поєднання таблиць cities та katottg_dictionary з декількома додатковими умовами AND та OR
<changeSet author="registry owner" id="or join by category">
    <ext:createSearchCondition name="cities_4_or_5_level_by_category">
        <ext:table name="cities" alias="ci">
            <ext:column name="city_id" />
            <ext:column name="name" searchType="startsWith" />
            <ext:column name="katottg" searchType="equal" />
        </ext:table>
        <ext:table name="katottg_dictionary" alias="kd">
            <ext:column name="name" alias="name_dict" />
        </ext:table>
        <ext:join type="left">
            <ext:left alias="ci">
                <ext:column name="katottg" />
            </ext:left>
            <ext:right alias="kd">
                <ext:column name="level4" />
            </ext:right>
            <ext:condition logicOperator="and" columnName="kd.category" operator="ne" value="'B'">
                <ext:condition logicOperator="or" columnName="ci.katottg" operator="eq" value="kd.add_level" />
                <ext:condition logicOperator="and" columnName="kd.category" operator="eq" value="'B'"/>
            </ext:condition>
        </ext:join>
    </ext:createSearchCondition>
</changeSet>
На виході Liquibase генерує наступний SQL-запит:
Приклад 12. SQL-запит. Використання LEFT JOIN з декількома додатковими умовами AND та OR
SELECT ci.city_id, ci.name, ci.katottg, kd.name AS name_dict
  FROM cities AS ci LEFT JOIN katottg_dictionary AS kd
    ON (ci.katottg = kd.level4) and (kd.category = 'B')
       or ((ci.katottg = kd.add_level) and (kd.category <> 'B'));

Зверніть увагу, що вкладені умови <ext:condition> у коді групуються дужками ().

Для цього прикладу групування не є обов’язковим: без дужок код виконається так само як і з дужками, оскільки умови AND мають вище положення за пріоритетом, ніж OR, і будуть визначені першими.

Зверніть увагу на цю особливість для можливого використання за інших умов.