Технические данные:
PHP: 7.4.20
MySQL: 5.7.34-37
Main module: 21.300.0.
Ошибка:
[1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
(
SELECT
`main_user_access`.`USER_ID` AS `IU_VALUE`
FROM `b_u' at line 14!
Была обнаружена в ходе решения боевой задачи по получению всех ID лидов, которые доступны текущему пользователю.
Лид считается доступным, если принадлежит текущему пользователю или ответственный за него находится в любом подразделении на уровень ниже.
То есть: руководитель видит себя + всех вложенных сотрудников, сотрудник подразделения видит себя + всех вложенных (но не своего подразделения и не своего руководителя).
Предполагаемое решение: выполнить Query на лиды с фильтром используя UNION подзапрос (первый запрос на получение подчиненных + себя, второй на нижестоящие подразделения).
Код:
use \Bitrix\Main,
\Bitrix\Crm;
Main\Loader::requireModule('crm');
// Формируем UNION запрос
/**
* Идентификатор искомого пользователя
* @var integer
*/
$interestingUserId = 1;
/**
* Для упрощения, тут содержатся access code подразделений
* вложенных нижестоящих от текущего подразделения пользователя
* @var string[]
*/
$nestedList = [
'DR1',
'DR2'
];
/**
* Будущий запрос с union который должен вернуть ID пользователей
* Первый запрос: возвращает ID себя + вложенных подразделений (если он руководитель).
* @var Query
*/
$userAccessCodeQuery = Main\UserAccessTable::query()
->setSelect(['IU_VALUE'])
->registerRuntimeField(
new Main\ORM\Fields\ExpressionField(
'IU_VALUE',
'SUBSTR(%s, 3)',
'ACCESS_CODE'
)
)
->where('USER_ID', $interestingUserId)
->whereLike('ACCESS_CODE', 'IU%');
/**
* Подзапрос на вложенные подразделения
* @var Query
*/
$subQuery = Main\UserAccessTable::query()
->setSelect(['IU_VALUE' => 'USER_ID'])
->whereIn('ACCESS_CODE', $nestedList);
$userAccessCodeQuery->union($subQuery);
/**
* Получаем запрос
*/
$activityQuery = Crm\LeadTable::query()
->setSelect([
'ID',
'ASSIGNED_BY_ID',
])
->whereIn('ASSIGNED_BY_ID', $accessQuery)
;
echo $activityQuery->getQuery();
Генерируемый запрос (не рабочий):
SELECT
`crm_lead`.`ID` AS `ID`,
`crm_lead`.`ASSIGNED_BY_ID` AS `ASSIGNED_BY_ID`
FROM `b_crm_lead` `crm_lead`
WHERE
`crm_lead`.`ASSIGNED_BY_ID` IN (
(SELECT
SUBSTR(`main_user_access`.`ACCESS_CODE`, 3) AS `IU_VALUE`
FROM `b_user_access` `main_user_access`
WHERE
`main_user_access`.`USER_ID` = 1
AND `main_user_access`.`ACCESS_CODE` LIKE 'IU%'
)
UNION
(
SELECT
`main_user_access`.`USER_ID` AS `IU_VALUE`
FROM `b_user_access` `main_user_access`
WHERE
`main_user_access`.`ACCESS_CODE` IN ('DR1', 'DR2')
)
)
Если в этом запросе убрать лишние скобки обрамляющие каждый подзапрос, т.е. было ((x) UNION (y)) превратить в (x UNION y) все станет работать.
Сейчас мы используем "окольный путь", не указываем Query в whereIn явно, а используем прослойку в виде таблицы:
$activityQuery = Crm\LeadTable::query()
->setSelect([
'ID',
'ASSIGNED_BY_ID',
])
->whereIn('ASSIGNED_BY_ID', new Main\DB\SqlExpression("SELECT IU_VALUE FROM (".$userAccessCodeQuery->getQuery().") as responsible_subquery_tmp_table"))
;
echo $activityQuery->getQuery();
Тогда запрос получается исполняемым (работает):
SELECT
`crm_lead`.`ID` AS `ID`,
`crm_lead`.`ASSIGNED_BY_ID` AS `ASSIGNED_BY_ID`
FROM `b_crm_lead` `crm_lead`
WHERE
`crm_lead`.`ASSIGNED_BY_ID` IN (
SELECT IU_VALUE FROM (
(
SELECT
SUBSTR(`main_user_access`.`ACCESS_CODE`, 3) AS `IU_VALUE`
FROM `b_user_access` `main_user_access`
WHERE `main_user_access`.`USER_ID` = 1 AND `main_user_access`.`ACCESS_CODE` LIKE 'IU%'
)
UNION
(
SELECT
`main_user_access`.`USER_ID` AS `IU_VALUE`
FROM `b_user_access` `main_user_access`
WHERE `main_user_access`.`ACCESS_CODE` IN ('DR1', 'DR2')
)
) as responsible_subquery_tmp_table
)
Но время подобного запроса, а так же план его выполнения оставляет желать лучшего.
Запрос с временной таблицей: 0.00146 сек
Запрос без скобок: 0.00029 сек
Т.е. запрос без скобок обрамляющих запрос работает в 5 раз быстрее чем создание временной таблицы.
Время получено на чистом битриксе без нагрузки, на реальной системе порядок сохраняется, но цифры другие естественно.
Отсюда вытекают следующие вопросы:
Является ли это штатным поведением или ошибкой UNION?
Если это штатное поведение, то можно ли как-то его обойти и не добавлять обрамляющие скобки?
Понимаю что решение писать свой DB\SqlExpression есть всегда, но решение выглядит сомнительным.
Технические данные: PHP: 7.4.20 MySQL: 5.7.34-37 Main module: 21.300.0.
Ошибка:
Была обнаружена в ходе решения боевой задачи по получению всех ID лидов, которые доступны текущему пользователю. Лид считается доступным, если принадлежит текущему пользователю или ответственный за него находится в любом подразделении на уровень ниже. То есть: руководитель видит себя + всех вложенных сотрудников, сотрудник подразделения видит себя + всех вложенных (но не своего подразделения и не своего руководителя).
Предполагаемое решение: выполнить Query на лиды с фильтром используя UNION подзапрос (первый запрос на получение подчиненных + себя, второй на нижестоящие подразделения). Код:
Генерируемый запрос (не рабочий):
Если в этом запросе убрать лишние скобки обрамляющие каждый подзапрос, т.е. было
((x) UNION (y))
превратить в(x UNION y)
все станет работать.Сейчас мы используем "окольный путь", не указываем Query в whereIn явно, а используем прослойку в виде таблицы:
Тогда запрос получается исполняемым (работает):
Но время подобного запроса, а так же план его выполнения оставляет желать лучшего. Запрос с временной таблицей: 0.00146 сек Запрос без скобок: 0.00029 сек Т.е. запрос без скобок обрамляющих запрос работает в 5 раз быстрее чем создание временной таблицы. Время получено на чистом битриксе без нагрузки, на реальной системе порядок сохраняется, но цифры другие естественно.
Отсюда вытекают следующие вопросы:
Понимаю что решение писать свой DB\SqlExpression есть всегда, но решение выглядит сомнительным.