join запросы ORM

Работать будем с тремя тестовыми таблицами, которые можно создать и заполнить SQL запросом:

-- создаем базу данных 
CREATE DATABASE IF NOT EXISTS `название_базы`
USE `название_базы`;
-- дамп структуры для таблица CITY
CREATE TABLE IF NOT EXISTS `CITY` (
`id` int(7) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`city_type_id` int(7) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- дамп данных таблицы CITY
INSERT INTO `CITY` (`id`, `name`, `city_type_id`) VALUES
(1, 'Москва', 8),
(2, 'Санкт-Петербург', 8),
(3, 'Уфа', 8),
(4, 'Домодедово', 5),
(5, 'Сарапул', 5),
(6, ' Циолковский', 2);
-- дамп структуры для таблица CITYZEN
CREATE TABLE IF NOT EXISTS `CITYZEN` (
`id` int(7) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`city_id` int(7) NOT NULL,
`responsible` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='citizen';
-- дамп данных таблицы CITYZEN
INSERT INTO `CITYZEN` (`id`, `name`, `city_id`, `responsible`) VALUES
(1, 'Маша', 1, 1),
(2, 'Гриша', 2, 0),
(3, 'Даша', 1, 1),
(4, 'Коля', 3, 0),
(5, 'Дима', 4, 1),
(6, 'Вика', 4, 1),
(7, 'Люба', 5, 1),
(8, 'Кирилл', 3, 0),
(9, 'Анатолий', 3, 1),
(10, 'Вова', 4, 0),
(11, 'Витя', 4, 1);
-- дамп структуры для таблица CITY_TYPE
CREATE TABLE IF NOT EXISTS `CITY_TYPE` (
`id` int(7) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='city_type';
-- дамп данных таблицы CITY_TYPE
/*!40000 ALTER TABLE `CITY_TYPE` DISABLE KEYS */;
INSERT INTO `CITY_TYPE` (`id`, `name`) VALUES
(1, 'самые малы'),
(2, 'малые'),
(3, 'полусредни'),
(4, 'полусредни'),
(5, 'средние'),
(6, 'крупные'),
(7, 'крупнейшие'),
(8, 'Города-миллионеры');

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

Задача

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

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, работать с ним интуитивно понятно:

// подключаем класс для вывода SQL запроса
$connection = Bitrix\Main\Application::getConnection();
// старт трекера
$tracker = $connection->startTracker();
// аргумент в конструкторе класса Query, это сущность которая соответсвует таблице в секциии FROM
$query = new \Bitrix\Main\Entity\Query(
    // сущность основной таблицы
    lib\CityzenTable::getEntity()
);
// регистрируем новое временное поле для исходной сущности
$query->registerRuntimeField(
    // поле CITY как ссылка на таблицу CITY
    'CITY',
    [
        // сущность присоединяемой таблицы
        'data_type' => lib\CityTable::getEntity(),
        // указываем способ связывания, this указывает на основную сущность 
        'reference' => [
            // this.city_id относится к основной таблице CITYZEN поле city_id, ref.id на сущность указанную в data_type 
            '=this.city_id' => 'ref.id',
        ],
        // тип присоединения таблицы
        'join_type' => "LEFT"
    ]
);
// регистрируем новое временное поле для исходной сущности
$query->registerRuntimeField(
    // поле CITY_TYPE как ссылка на таблицу CITY_TYPE
    'CITY_TYPE',
    [
        // сущность присоединяемой таблицы
        'data_type' => lib\CityTypeTable::getEntity(),
        // указываем способ связывания, this указывает на основную сущность 
        'reference' => [
            // this.CITY.city_type_id относится к основной таблице CITY поле city_type_id, ref.id на сущность указанную в data_type 
            '=this.CITY.city_type_id' => 'ref.id',
        ],
        // тип присоединения таблицы
        'join_type' => "LEFT"
    ]
);
// секция селект
$query->setSelect([
    // поле name основной таблицы
    'name',
    // поле id основной таблицы
    'id',
    // поле city_id основной таблицы
    'city_id',
    // поле city_id основной таблицы
    'responsible',
    // поле CITY.name вспомогательной таблицы будет доступно по ключу city_name
    'city_name' => 'CITY.name',
    // поле CITY_TYPE.name вспомогательной таблицы будет доступно по ключу city_type_name
    'city_type_name' => 'CITY_TYPE.name',
]);
// выполняем запрос
$result = $query->exec();
// стоп трекера
$connection->stopTracker();
// вывод sql запроса
echo '<pre>';
foreach ($tracker->getQueries() as $query) {
    // текст запроса
    var_dump($query->getSql());
}
echo '</pre>';
// распечатка массива
pp($result->fetchAll());

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

// сущность основной таблицы
$dbCityzenInfo = lib\CityzenTable::getList([
    // секция селект
    'select' => [
        // поле name основной таблицы
        'name',
        // поле id основной таблицы
        'id',
        // поле city_id основной таблицы
        'city_id',
        // поле city_id основной таблицы
        'responsible',
        // поле CITY.name вспомогательной таблицы будет доступно по ключу city_name
        'city_name' => 'CITY.name',
        // поле CITY_TYPE.name вспомогательной таблицы будет доступно по ключу city_type_name
        'city_type_name' => 'CITY_TYPE.name',
    ],
    // динамически определенные поля
    'runtime' => array(
        // поле CITY как ссылка на таблицу CITY
        'CITY' => [
            // сущность присоединяемой таблицы
            'data_type' => lib\CityTable::getEntity(),
            // указываем способ связывания, this указывает на основную сущность 
            'reference' => [
                // this.city_id относится к основной таблице CITYZEN поле city_id, ref.id на сущность указанную в data_type 
                '=this.city_id' => 'ref.id'
            ],
            // тип присоединения таблицы
            'join_type' => 'LEFT'
        ],
        // поле CITY_TYPE как ссылка на таблицу CITY_TYPE
        'CITY_TYPE' => [
            // сущность присоединяемой таблицы
            'data_type' => lib\CityTypeTable::getEntity(),
            // указываем способ связывания, this указывает на основную сущность 
            'reference' => [
                // this.CITY.city_type_id относится к основной таблице CITY поле city_type_id, ref.id на сущность указанную в data_type
                '=this.CITY.city_type_id' => 'ref.id',
            ],
            // тип присоединения таблицы
            'join_type' => 'LEFT'
        ],
    ),
]);
// распечатка массива
pp($dbCityzenInfo->fetchAll());