join запросы в битрикс

Работать будем с тремя таблицами:

CITYZEN — гражданин

id
name
city_id
responsible(1- ответственный, 0-безответсвенные)

сложные join запросы в битрикс 2
CITY — город

id
name
city_type_id

сложные join запросы в битрикс
CITY_TYPE — тип города

id
name

сложные join запросы в битрикс 3
SQL на создание таблиц можете найти тут

На их основе я сгенерировал 3 ORM-класса: CityzenTable, CityTable и CityTypeTable, на основе этой инструкции

Задача 1. Получить табличку из ответственных граждан: имя гражданина , название города гражданина и название типа города.
В SQL запрос выглядел бы так:

?
1
2
3
4
5
SELECT u.name as cityzen_name, с.name as city_name, ct.name as city_type_name
FROM CITYZEN u
LEFT JOIN CITY с on u.city_id = с.id
LEFT JOIN CITY_TYPE ct on ct.id = с.city_type_id
WHERE u.responsible = 1

Для построения запроса будем использовать класс \Bitrix\Main\Entity\Query.
Работать с ним интуитивно понятно.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?php
 
use \Bitrix\Main\Entity\Query;
 
//Создаем экземпляр класса
//Аргумент в конструкторе класса Query - это сущность, которая соответсвует таблице в секциии FROM
$query = new \Bitrix\Main\Entity\Query(CityzenTable::getEntity());
 
 
//вынес имя таблиц в переменные , она пригодятся нам несколько раз
$cityTableName = 'CITY';
$cityzenTableName = 'CITYZEN';
$cityTypeTableName = 'CITY_TYPE';
//Чтобы сделать сложный запрос с присоединением таблиц(left right join) можно использовать registerRuntimeField
$query->registerRuntimeField($cityTableName, [
        'data_type' => CityTable::getEntity(),
        'reference' => [
            '=this.city_id' => 'ref.id',
        ],
        'join_type' => "LEFT"
    ]
);

Рассмотрим аргументы метода registerRuntimeField:
Первый — это название поля, его мы сможем использовать далее в секциях: select и filter.
Второй аргумент массив:
data_type — указываем сущность(таблица, указанная в SQL запросе, после LEFT JOIN) которую будем джойнить
reference — указываем способ связывания this указывает на сущность CityzenTable , ref на сущность указанную в data_type — CityTable
join_type — тип присоединения таблицы Возможные значения: LEFT(по умолчанию), RIGHT и INNER

по аналогии присоединяем таблицу с типами городов

?
1
2
3
4
5
6
7
8
$query->registerRuntimeField($cityTypeTableName, [
        'data_type' => CityTypeTable::getEntity(),
        'reference' => [
            '=this.' . $cityTableName . '.city_type_id' => 'ref.id',
        ],
        'join_type' => "LEFT"
    ]
);

В кусочке кода выше можно отметить, как устанавливается связь третьей таблицы ко второй. Нельзя указать просто $cityTableName . '.city_type_id', т.к. использование this обязательно!

Далее указываем секцию селект:

?
1
2
3
4
5
$query->setSelect([
    'cityzen_name' => $cityzenTableName . '.name',
    'city_name' => $cityTableName . '.name',
    'city_type_name' => $cityTypeTableName . '.name',
]);

В результате значение имени юзера будет доступно по ключу cityzen_name, а название группы по city_name.

?
1
2
3
$query->setFilter([
    $cityzenTableName . '.responsible' => 1
]);

Если возникнет необходимость ограничить выборку одним элементом, то применяем:

?
1
$query->setLimit(1);

Когда формирование запроса закончено, нужно выполнить запрос:

?
1
$dbCityzenInfo = $query->exec();

И получаем элементы выборки:

?
1
2
3
if ($resItem = $dbCityzenInfo->fetch()){
    var_dump($resItem );
}

Также можно было использовать функцию getlist вместо построителя запросов. Выглядеть это будет так:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
$dbCityzenInfo = CityzenTable::getList([
    'select' => [
        'cityzen_name' => $cityzenTableName . '.name',
        'city_name' => $cityTableName . '.name',
        'city_type_name' => $cityTypeTableName . '.name',
    ],
    'runtime' => array(
        $cityTableName => [
            'data_type' => CityTable::getEntity(),
            'reference' => [
                '=this.city_id' => 'ref.id'
            ],
            'join_type' => 'LEFT'
        ],
        $cityTypeTableName => [
            'data_type' => CityTypeTable::getEntity(),
            'reference' => [
                '=this.' . $cityTableName . '.city_type_id' => 'ref.id',
            ],
            'join_type' => 'LEFT'
        ],
    ),
    'filter' => [
        $cityzenTableName . '.responsible' => 1
    ],
    'limit' => 1
]);
if ($resItem = $dbCityzenInfo->fetch()){
    var_dump($resItem );
}

Фактически под запросом во втором варианте скрывается запрос 1-ого варианта, т.е. функция getlist выступает оберткой — дополнительным слоем абстракции.

Задача 2. На основе таблицы CITYZEN получить количество всех граждан из города Уфа(id 3).

?
1
2
3
4
5
6
7
8
9
10
11
12
$query = new \Bitrix\Main\Entity\Query(CityzenTable::getEntity());
$query->registerRuntimeField("CNT", [
        "data_type" => "integer",
        "expression" => ["count(id)"]
    ]
)
    ->setSelect(['CNT'])
    ->setFilter([
        'city_id' => 3,
    ])
    ->exec()
    ->fetch()['CNT'];

Хотелось бы проверить какой sql получится на выходе. Мы можем ориентироваться на результат, но в идеале хотелось бы увидеть какой код сгенерировал битрикс?
Скоро будет готова статья по отладке.

Полезный материал по сложным запросам в битрикс:

Рекомендуем к прочтению



Оставить комментарий

Этот сайт защищен reCAPTCHA и применяются Политика конфиденциальности и Условия обслуживания применять.

Срок проверки reCAPTCHA истек. Перезагрузите страницу.