join запросы ORM

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

Создание таблиц через SQL и работа через ORM

-- создаем базу данных
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(
// сущность основной таблицы
	\Hmarketing\Main\Model\CityzenTable::getEntity()
);
// регистрируем новое временное поле для исходной сущности
$query->registerRuntimeField(
// поле CITY как ссылка на таблицу CITY
	'CITY',
	[
		// сущность присоединяемой таблицы
		'data_type' => \Hmarketing\Main\Model\CitiTable::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' => \Hmarketing\Main\Model\CititypeTable::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 выступает оберткой, дополнительным слоем абстракции. Выглядеть это будет так:

// подключаем класс для вывода SQL запроса
$connection = Bitrix\Main\Application::getConnection();
// старт трекера
$tracker = $connection->startTracker();
// сущность основной таблицы
$result = \Hmarketing\Main\Model\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' => \Hmarketing\Main\Model\CitiTable::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' => \Hmarketing\Main\Model\CititypeTable::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'
		],
	),
]);
// стоп трекера
$connection->stopTracker();
// вывод sql запроса
echo '<pre>';
foreach ($tracker->getQueries() as $query) {
	// текст запроса
	var_dump($query->getSql());
}
echo '</pre>';
// распечатка массива
pp($result->fetchAll());

Подобный запрос, но в другом более современном синтаксисе:

// подключаем класс для вывода SQL запроса
$connection = Bitrix\Main\Application::getConnection();
// старт трекера
$tracker = $connection->startTracker();
// сущность основной таблицы
$result = \Hmarketing\Main\Model\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' => [
		new \Bitrix\Main\Entity\ReferenceField(
			// поле CITY как ссылка на таблицу CITY
			'CITY',
			// сущность присоединяемой таблицы
			'Hmarketing\Main\Model\CitiTable',
			// указываем способ связывания, this указывает на основную сущность
			array(
				// this.city_id относится к основной таблице CITYZEN поле city_id, ref.id на сущность указанную в data_type
				'=this.city_id' => 'ref.id'
			),
			// тип присоединения таблицы
			array('join_type' => 'LEFT')
		),
		new \Bitrix\Main\Entity\ReferenceField(
			// поле CITY_TYPE как ссылка на таблицу CITY_TYPE
			'CITY_TYPE',
			// сущность присоединяемой таблицы
			'Hmarketing\Main\Model\CititypeTable',
			// указываем способ связывания, this указывает на основную сущность
			array(
				// this.CITY.city_type_id относится к основной таблице CITY поле city_type_id, ref.id на сущность указанную в data_type
				'=this.CITY.city_type_id' => 'ref.id'
			),
			// тип присоединения таблицы
			array('join_type' => 'LEFT')
		)
	],
]);
// стоп трекера
$connection->stopTracker();
// вывод sql запроса
echo '<pre>';
foreach ($tracker->getQueries() as $query) {
	// текст запроса
	var_dump($query->getSql());
}
echo '</pre>';
// распечатка массива
pp($result->fetchAll());

Создание таблиц и работа через ORM

Таблицы можно создать через описание сущностей в классе через ORM, причем reference можно так же задать в классе, после этого в каждом запросе уже будут доступны присоединенные таблицы:

/local/modules/hmarketing.main/lib/model/cityzen.php<?php

namespace Hmarketing\Main\Model;

// пространство имен для ORM
use \Bitrix\Main\Entity;

// пространство имен для кеша
use \Bitrix\Main\Application;

// сущность ORM унаследованная от DataManager
class CityzenTable extends Entity\DataManager
{
    // название таблицы в базе данных, если не указывать данную функцию, то таблица в бд сформируется автоматически из неймспейса
    public static function getTableName()
    {
        return "CITYZEN";
    }

    // подключение к БД, если не указывать, то будет использовано значение по умолчанию подключения из файла .settings.php. Если указать, то можно выбрать подключение, которое может быть описано в .setting.php
    public static function getConnectionName()
    {
        return "default";
    }

    // метод возвращающий структуру ORM-сущности
    public static function getMap()
    {
        /*
         * Типы полей:
         * DatetimeField - дата и время
         * DateField - дата
         * BooleanField - логическое поле да/нет
         * IntegerField - числовой формат
         * FloatField - числовой дробный формат
         * EnumField - список, можно передавать только заданные значения
         * TextField - text
         * StringField - varchar
         */
        return array(
            new Entity\IntegerField(
                "id",
                array(
                    "primary" => true,
                    "autocomplete" => true,
                )
            ),
            new Entity\StringField(
                "name"
            ),
            new Entity\IntegerField(
                "city_id"
            ),
            new Entity\IntegerField(
                "responsible"
            ),
            // поле CITY как ссылка на таблицу CITY
            new Entity\ReferenceField(
                 // имя сущности
                'CITY',
                // связываемая сущность другой таблицы
                '\Hmarketing\Main\Model\CitiTable',
                // this - текущая сущность, ref - связываемая
                array('=this.city_id' => 'ref.id'),
                // тип присоединения таблицы
                array('join_type' => 'LEFT')
            ),
            // поле CITY_TYPE как ссылка на таблицу CITY_TYPE
            new Entity\ReferenceField(
                // имя сущности
                'CITY_TYPE',
                // связываемая сущность другой таблицы
                '\Hmarketing\Main\Model\CititypeTable',
                // this - текущая сущность, ref - связываемая
                array('=this.CITY.city_type_id' => 'ref.id'),
                // тип присоединения таблицы
                array('join_type' => 'LEFT')
            ),
        );
    }
}

/local/modules/hmarketing.main/lib/model/citi.php<?php

namespace Hmarketing\Main\Model;

// пространство имен для ORM
use \Bitrix\Main\Entity;
// пространство имен для кеша
use \Bitrix\Main\Application;

// сущность ORM унаследованная от DataManager
class CitiTable extends Entity\DataManager
{
    // название таблицы в базе данных, если не указывать данную функцию, то таблица в бд сформируется автоматически из неймспейса
    public static function getTableName()
    {
        return "CITY";
    }

    // подключение к БД, если не указывать, то будет использовано значение по умолчанию подключения из файла .settings.php. Если указать, то можно выбрать подключение, которое может быть описано в .setting.php
    public static function getConnectionName()
    {
        return "default";
    }

    // метод возвращающий структуру ORM-сущности
    public static function getMap()
    {
        /*
         * Типы полей:
         * DatetimeField - дата и время
         * DateField - дата
         * BooleanField - логическое поле да/нет
         * IntegerField - числовой формат
         * FloatField - числовой дробный формат
         * EnumField - список, можно передавать только заданные значения
         * TextField - text
         * StringField - varchar
         */
        return array(
            new Entity\IntegerField(
                "id",
                array(
                    "primary" => true,
                    "autocomplete" => true,
                )
            ),
            new Entity\StringField(
                "name"
            ),
            new Entity\IntegerField(
                "city_type_id"
            ),
        );
    }
}

/local/modules/hmarketing.main/lib/model/cititype.php<?php

namespace Hmarketing\Main\Model;

// пространство имен для ORM
use \Bitrix\Main\Entity;

// пространство имен для кеша
use \Bitrix\Main\Application;

// сущность ORM унаследованная от DataManager
class CititypeTable extends Entity\DataManager
{
    // название таблицы в базе данных, если не указывать данную функцию, то таблица в бд сформируется автоматически из неймспейса
    public static function getTableName()
    {
        return "CITY_TYPE";
    }

    // подключение к БД, если не указывать, то будет использовано значение по умолчанию подключения из файла .settings.php. Если указать, то можно выбрать подключение, которое может быть описано в .setting.php
    public static function getConnectionName()
    {
        return "default";
    }

    // метод возвращающий структуру ORM-сущности
    public static function getMap()
    {
        /*
         * Типы полей:
         * DatetimeField - дата и время
         * DateField - дата
         * BooleanField - логическое поле да/нет
         * IntegerField - числовой формат
         * FloatField - числовой дробный формат
         * EnumField - список, можно передавать только заданные значения
         * TextField - text
         * StringField - varchar
         */
        return array(
            new Entity\IntegerField(
                "id",
                array(
                    "primary" => true,
                    "autocomplete" => true,
                )
            ),
            new Entity\StringField(
                "name"
            ),
        );
    }
}

Заполнение данными

Заполним таблицы данными:

\Bitrix\Main\Entity\Base::getInstance("\Hmarketing\Main\Model\CitiTable")->createDbTable();
$citi = [
	'Москва' => 8,
	'Санкт-Петербург' => 8,
	'Уфа' => 8,
	'Домодедово' => 5,
	'Сарапул' => 5,
	'Циолковский' => 2,
];
foreach ($citi as $key => $value) {
	\Hmarketing\Main\Model\CitiTable::add(
		[
			"name" => $key,
			"city_type_id" => $value
		]
	);
}

\Bitrix\Main\Entity\Base::getInstance("\Hmarketing\Main\Model\CityzenTable")->createDbTable();
$cityzen = [
	'Маша' => [1,1],
	'Гриша' => [2,0],
	'Даша' => [1,1],
	'Коля' => [3,0],
	'Дима' => [4,1],
	'Вика' => [4,1],
	'Люба' => [5,1],
	'Кирилл' => [3,0],
	'Анатолий' => [3,1],
	'Вова' => [4,0],
	'Витя' => [4,1],
];
foreach ($cityzen as $key => $value) {
	\Hmarketing\Main\Model\CityzenTable::add(
		[
			"name" => $key,
			"city_id" => $value[0],
			"responsible" => $value[1],

		]
	);
}

\Bitrix\Main\Entity\Base::getInstance("\Hmarketing\Main\Model\CititypeTable")->createDbTable();
$citiType = [
	'самые малы',
	'малые',
	'полусредни',
	'полусредни',
	'средние',
	'крупные',
	'крупнейшие',
	'Города-миллионеры'
];
foreach ($citiType as $key => $value) {
	\Hmarketing\Main\Model\CititypeTable::add(
		[
			"name" => $value,
		]
	);
}

Запрос

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(
	// сущность основной таблицы
	\Hmarketing\Main\Model\CityzenTable::getEntity()
);
// секция селект
$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());

// подключаем класс для вывода SQL запроса
$connection = Bitrix\Main\Application::getConnection();
// старт трекера
$tracker = $connection->startTracker();
$result = \Hmarketing\Main\Model\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',
	]
]);
// стоп трекера
$connection->stopTracker();
// вывод sql запроса
echo '<pre>';
foreach ($tracker->getQueries() as $query) {
	// текст запроса
	var_dump($query->getSql());
}
echo '</pre>';
// распечатка массива
pp($result->fetchAll());