static function getRelatedJoins($select, $filter, $order, $params)
{
$relatedJoins = [];
$userId = ($params['USER_ID'] ? (int)$params['USER_ID'] : User::getId());
$viewedBy = (int)($params['VIEWED_BY'] ?? $userId);
$sortingGroupId = (int)($params['SORTING_GROUP_ID'] ?? 0);
$joinAlias = $params['JOIN_ALIAS'] ?? '';
$sourceAlias = $params['SOURCE_ALIAS'] ?? 'T';
$filterKeys = static::GetFilteredKeys($filter);
$possibleJoins = [
'CREATOR',
'RESPONSIBLE',
'VIEWED',
'SORTING',
'FAVORITE',
'STAGES',
'FORUM',
'FORUM_MESSAGE',
'USER_OPTION',
'COUNTERS',
'SCRUM',
'SCENARIO',
'IM_CHAT',
];
foreach ($possibleJoins as $join)
{
switch ($join)
{
case 'CREATOR':
if (
in_array('CREATED_BY_NAME', $select, true)
|| in_array('CREATED_BY_LAST_NAME', $select, true)
|| in_array('CREATED_BY_SECOND_NAME', $select, true)
|| in_array('CREATED_BY_LOGIN', $select, true)
|| in_array('CREATED_BY_WORK_POSITION', $select, true)
|| in_array('CREATED_BY_PHOTO', $select, true)
|| array_key_exists('ORIGINATOR_NAME', $order)
|| array_key_exists('CREATED_BY', $order)
)
{
$tableName = UserTable::getTableName();
$relatedJoins[$join] = "INNER JOIN {$tableName} {$joinAlias}CU "
. "ON {$joinAlias}CU.ID = {$sourceAlias}.CREATED_BY";
}
break;
case 'RESPONSIBLE':
if (
in_array('RESPONSIBLE_NAME', $select, true)
|| in_array('RESPONSIBLE_LAST_NAME', $select, true)
|| in_array('RESPONSIBLE_SECOND_NAME', $select, true)
|| in_array('RESPONSIBLE_LOGIN', $select, true)
|| in_array('RESPONSIBLE_WORK_POSITION', $select, true)
|| in_array('RESPONSIBLE_PHOTO', $select, true)
|| array_key_exists('RESPONSIBLE_NAME', $order)
|| array_key_exists('RESPONSIBLE_ID', $order)
)
{
$tableName = UserTable::getTableName();
$relatedJoins[$join] = "INNER JOIN {$tableName} {$joinAlias}RU "
. "ON {$joinAlias}RU.ID = {$sourceAlias}.RESPONSIBLE_ID";
}
break;
case 'VIEWED':
if (
in_array('STATUS', $select, true)
|| in_array('NOT_VIEWED', $select, true)
|| in_array('VIEWED_DATE', $select, true)
|| in_array('STATUS', $filterKeys, true)
|| in_array('VIEWED_BY', $filterKeys, true)
|| in_array('WITH_NEW_COMMENTS', $filterKeys, true)
)
{
$tableName = ViewedTable::getTableName();
$relatedJoins[$join] = "LEFT JOIN {$tableName} {$joinAlias}TV "
. "ON {$joinAlias}TV.TASK_ID = {$sourceAlias}.ID AND {$joinAlias}TV.USER_ID = {$viewedBy}";
}
break;
case 'SORTING':
if (
in_array('SORTING', $select, true)
|| in_array('SORTING', $filterKeys, true)
|| array_key_exists('SORTING', $order)
)
{
$tableName = SortingTable::getTableName();
$relatedJoins[$join] = "LEFT JOIN {$tableName} {$joinAlias}SRT "
. "ON {$joinAlias}SRT.TASK_ID = {$sourceAlias}.ID "
. "AND " . (
$sortingGroupId > 0
? "{$joinAlias}SRT.GROUP_ID = {$sortingGroupId}"
: "{$joinAlias}SRT.USER_ID = {$userId}"
);
}
break;
case 'FAVORITE':
if (
in_array('FAVORITE', $select, true)
|| in_array('FAVORITE', $filterKeys, true)
|| array_key_exists('FAVORITE', $order)
)
{
$tableName = FavoriteTable::getTableName();
$relatedJoins[$join] = "LEFT JOIN {$tableName} {$joinAlias}FVT "
. "ON {$joinAlias}FVT.TASK_ID = {$sourceAlias}.ID AND {$joinAlias}FVT.USER_ID = {$userId}";
}
break;
case 'STAGES':
if (in_array('STAGES_ID', $filterKeys, true))
{
$tableName = TaskStageTable::getTableName();
$relatedJoins[$join] = "INNER JOIN {$tableName} {$joinAlias}STG "
. "ON STG.TASK_ID = {$sourceAlias}.ID";
}
break;
case 'FORUM':
if (!BitrixMainLoader::includeModule('forum'))
{
break;
}
if (
in_array('COMMENTS_COUNT', $select, true)
|| in_array('SERVICE_COMMENTS_COUNT', $select, true)
|| in_array('FORUM_ID', $select, true)
)
{
$tableName = BitrixForumTopicTable::getTableName();
$relatedJoins[$join] = "LEFT JOIN {$tableName} {$joinAlias}FT "
. "ON {$joinAlias}FT.ID = {$sourceAlias}.FORUM_TOPIC_ID";
}
break;
case 'FORUM_MESSAGE':
if (!BitrixMainLoader::includeModule('forum'))
{
break;
}
if (in_array('WITH_NEW_COMMENTS', $filterKeys, true))
{
$tableName = BitrixForumMessageTable::getTableName();
$relatedJoins[$join] = "LEFT JOIN {$tableName} {$joinAlias}FM "
. "ON {$joinAlias}FM.TOPIC_ID = {$sourceAlias}.FORUM_TOPIC_IDn";
$relatedJoins[$join] .= "LEFT JOIN b_uts_forum_message {$joinAlias}BUF_FM "
. "ON {$joinAlias}BUF_FM.VALUE_ID = {$joinAlias}FM.ID";
}
break;
case 'USER_OPTION':
if (
array_key_exists('IS_PINNED', $order)
|| array_key_exists('IS_PINNED_IN_GROUP', $order)
)
{
$tableName = UserOptionTable::getTableName();
$relatedJoins[$join] = "LEFT JOIN {$tableName} {$joinAlias}TUO "
. "ON {$joinAlias}TUO.TASK_ID = {$sourceAlias}.ID AND {$joinAlias}TUO.USER_ID = {$userId}";
}
break;
case 'COUNTERS':
if (
in_array('WITH_COMMENT_COUNTERS', $filterKeys, true)
|| in_array('PROJECT_EXPIRED', $filterKeys, true)
|| in_array('PROJECT_NEW_COMMENTS', $filterKeys, true)
)
{
$tableName = CounterCounterTable::getTableName();
$relatedJoins[$join] = "LEFT JOIN {$tableName} {$joinAlias}TSC "
. "ON {$joinAlias}TSC.TASK_ID = {$sourceAlias}.ID AND {$joinAlias}TSC.USER_ID = {$userId}";
}
break;
case 'SCRUM':
$isScrumRequest = isset($filter['SCRUM_TASKS']) && ($filter['SCRUM_TASKS'] === 'Y');
$hasStatusKey = (
in_array('REAL_STATUS', $filterKeys, true)
&& self::containCompletedInActiveSprintStatus($filter)
);
$hasStoryPointsKey = in_array('STORY_POINTS', $filterKeys, true);
$hasEpicKey = in_array('EPIC', $filterKeys, true);
$scrumJoin = '';
$statusJoin = '';
$storyPointsJoin = '';
$epicJoin = '';
$storyPointsValue = null;
$epicValue = null;
foreach (static::getFilteredValues($filter) as $filterValue)
{
if ($hasStoryPointsKey && isset($filterValue['STORY_POINTS']))
{
$storyPointsValue = $filterValue['STORY_POINTS'];
}
if ($hasEpicKey && isset($filterValue['EPIC']))
{
$epicValue = $filterValue['EPIC'];
}
}
if ($isScrumRequest)
{
$scrumEntityTableName = EntityTable::getTableName();
$scrumItemTableName = ItemTable::getTableName();
$scrumJoin = " INNER JOIN {$scrumEntityTableName} {$joinAlias}BTSE
ON {$joinAlias}BTSE.GROUP_ID = {$sourceAlias}.GROUP_ID
";
if (isset($filter['SCRUM_ENTITY_IDS']))
{
$entityIds = $filter['SCRUM_ENTITY_IDS'];
$scrumJoin .= "AND {$joinAlias}BTSE.ID IN (" . implode(', ', $entityIds) . ")";
}
$scrumJoin .= " INNER JOIN {$scrumItemTableName} {$joinAlias}BTSI
ON {$joinAlias}BTSI.SOURCE_ID = {$sourceAlias}.ID
AND {$joinAlias}BTSI.ENTITY_ID = {$joinAlias}BTSE.ID
AND {$joinAlias}BTSI.ACTIVE = 'Y'
";
}
if ($hasStatusKey)
{
$scrumEntityTableName = EntityTable::getTableName();
$scrumItemTableName = ItemTable::getTableName();
$activeSprintStatus = EntityForm::SPRINT_ACTIVE;
$statusJoin = " LEFT JOIN {$scrumEntityTableName} {$joinAlias}TSE
ON {$joinAlias}TSE.GROUP_ID = {$sourceAlias}.GROUP_ID
AND {$joinAlias}TSE.STATUS = '{$activeSprintStatus}'
";
$statusJoin .= " LEFT JOIN {$scrumItemTableName} {$joinAlias}TSI
ON {$joinAlias}TSI.SOURCE_ID = {$sourceAlias}.ID
AND {$joinAlias}TSI.ENTITY_ID = {$joinAlias}TSE.ID
";
}
if ($hasStoryPointsKey)
{
$scrumEntityTableName = EntityTable::getTableName();
$scrumItemTableName = ItemTable::getTableName();
$storyPointsJoin = " INNER JOIN {$scrumEntityTableName} {$joinAlias}TSES
ON {$joinAlias}TSES.GROUP_ID = {$sourceAlias}.GROUP_ID
";
$storyPointsJoin .= " INNER JOIN {$scrumItemTableName} {$joinAlias}TSIS
ON {$joinAlias}TSIS.SOURCE_ID = {$sourceAlias}.ID
AND {$joinAlias}TSIS.ENTITY_ID = {$joinAlias}TSES.ID
";
if ($storyPointsValue === 'Y')
{
$storyPointsJoin .= " AND NULLIF({$joinAlias}TSIS.STORY_POINTS, '') IS NOT NULL";
}
else
{
$storyPointsJoin .= " AND NULLIF({$joinAlias}TSIS.STORY_POINTS, '') IS NULL";
}
}
if ($hasEpicKey)
{
$epicId = (int)$epicValue;
if ($epicId)
{
$scrumEntityTableName = EntityTable::getTableName();
$scrumItemTableName = ItemTable::getTableName();
$epicJoin = " INNER JOIN {$scrumEntityTableName} {$joinAlias}TSEE
ON {$joinAlias}TSEE.GROUP_ID = {$sourceAlias}.GROUP_ID
";
$epicJoin .= " INNER JOIN {$scrumItemTableName} {$joinAlias}TSIE
ON {$joinAlias}TSIE.SOURCE_ID = {$sourceAlias}.ID
AND {$joinAlias}TSIE.ENTITY_ID = {$joinAlias}TSEE.ID
AND {$joinAlias}TSIE.EPIC_ID = '{$epicId}'
";
}
}
$relatedJoins[$join] = $scrumJoin . $statusJoin . $storyPointsJoin . $epicJoin;
break;
case 'SCENARIO':
if (
in_array('SCENARIO_NAME', $select, true)
|| in_array('SCENARIO_NAME', $filterKeys, true)
|| array_key_exists('SCENARIO_NAME', $order)
)
{
$tableName = BitrixTasksInternalsTaskScenarioTable::getTableName();
$relatedJoins[$join] = "LEFT JOIN {$tableName} {$joinAlias}SCR "
. "ON {$joinAlias}SCR.TASK_ID = {$sourceAlias}.ID";
}
break;
case 'IM_CHAT':
if (
!MainLoader::includeModule('im')
|| !class_exists(BitrixImModelLinkTaskTable::class)
)
{
break;
}
if (!in_array('IM_CHAT_CHAT_ID', $filterKeys))
{
break;
}
$dialogId = (int) $filter['IM_CHAT_CHAT_ID'];
$dialogJoin = "
INNER JOIN ". BitrixImModelLinkTaskTable::getTableName() ." {$joinAlias}CTT
ON {$joinAlias}CTT.TASK_ID = {$sourceAlias}.ID
AND {$joinAlias}CTT.CHAT_ID = {$dialogId}
";
$relatedJoins[$join] = $dialogJoin;
break;
}
}
return $relatedJoins;
}