History Timeline sidebarWidget don't work in custom modules

[scicali]

History Timeline sidebarWidget don't work in custom modules

Issue

History Timeline sidebarWidget return error if u set in custom module detailviewdefs

Expected Behavior

History Timeline sidebarWidget must show audits

Actual Behavior

Set in custom module detailviewdefs.php history timeline widget

      'sidebarWidgets' => [
          [
              'type' => 'history-timeline',
              'acls' => [
                  'Accounts' => ['view', 'list']
              ]
          ],
      ],

in suitecrm there is mysql syntax error:

Thu Feb 17 09:25:51 2022 [10324][1][FATAL] SubpanelCustomQueryPort: Error executing custom query Query Failed:  UNION ALL  UNION ALL
 SELECT max(id),
        GROUP_CONCAT(field_name) as name,
        GROUP_CONCAT(after_value_string) as status,
        max(audit.date_created) AS date_modified,
        max(audit.date_created) AS date_entered,
        max(audit.date_created) AS date_end,
        audit.created_by AS assigned_user_id,
        'audit' panel_name   FROM gcrm_vod_contracts_line_items_audit as audit  WHERE parent_id = '22884552-7026-a699-a5a2-620cc1d09
79a'  GROUP BY audit.date_created, audit.created_by   ORDER BY date_end DESC Limit 0, 10: MySQL error 1064: You have an error in you
r SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION ALL  UNION AL
L SELECT max(id),
        GROUP_CONCAT(field_name) as name,
 ' at line 1

Possible Fix

Some custom modules can not have a relation with "history" and "activities" modules type;
in core\backend\Data\LegacyHandler\PresetDataHandlers\HistoryTimelineDataHandler.php, Line 278
you can handle this
from:

        $combinedQuery = $historyUnionQuery .
            ' UNION ALL ' . $activitiesUnionQuery .
            ' UNION ALL ' . $auditUnionQuery .
            ' ORDER BY ' . $sort['orderBy'] . ' ' . $sort['sortOrder'] . ' Limit ' . $selectOffset . ', ' . $selectLimit;

to

        $combinedQuery = '';
		if (strlen($historyUnionQuery)){
			$combinedQuery .= $historyUnionQuery . ' UNION ALL ';
		}
		if (strlen($activitiesUnionQuery)){
			$combinedQuery .= $activitiesUnionQuery . ' UNION ALL ';
		}
		$combinedQuery .= $auditUnionQuery .
            ' ORDER BY ' . $sort['orderBy'] . ' ' . $sort['sortOrder'] . ' Limit ' . $selectOffset . ', ' . $selectLimit;

Steps to Reproduce

1. Create custom module
2. Set in custom module detailviewdefs.php history timeline widget

      'sidebarWidgets' => [
          [
              'type' => 'history-timeline',
              'acls' => [
                  'Accounts' => ['view', 'list']
              ]
          ],
      ],

3. Go in Detail View

Your Environment

SuiteCRM Version used: 8.0.1
Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Firefox 94
Environment name and version (e.g. MySQL, PHP 7): php 7.4
Operating System and version (e.g Ubuntu 16.04): debian 11