SuiteCRM - Issue in Report module - Order/Sort related (field type) columns issue

💬 General Discussion
1

Hi all,

I want to discuss an issue in the Report module.
I found an issue in the Report module. It looks incorrect when sorting related fields.
For example:
I want to simply list all Student records (with Student Name, School (related field)) and sort by
School. But the result doesn’t sort by School name, it’s sorted by School ID (record UUID) instead.
It works with relationship fields but doesn’t work with related fields. (I mean with relationship fields, it corrects sort by name, but will sort by UUID with related fields).

image
image3089×1596 234 KB

I debugged and get the report query:

SELECT
	`students`.id AS 'students_id',
	`students`.NAME AS 'Student_name0',
	`students`.school_id AS 'School1' 
FROM `students`
WHERE	students.deleted = 0 
ORDER BY	`students`.school_id ASC

So I think we should add the jion (left jion) here with the school table and sort by school.name instead.
The file path: modules/AOR_Reports/AOR_Report.php, line 1369, ( if ($field->sort_by != ‘’) {)

 if($data['type'] == 'id' && !$field->field_function) {
    foreach ($field_module->field_defs as $def) {
        if(isset($def['id_name']) && $def['id_name'] == $field->field) {

            $relBean = BeanFactory::newBean($def['module']);
            $relAlias = "customjt{$i}";
            $query['join'][] = " LEFT JOIN $relBean->table_name as $relAlias ON $relAlias.id = $select_field AND $relAlias.deleted = 0 ";
            $query['sort_by'][] = $relAlias.".name " . $field->sort_by; // fixed the 'name' here just for testing, it need to refactor to get the correct db-columns
            break;
        }
    }
}

I’m using SuiteCRM v7.11.15, I also checked the latest version but the issue above still exists.
I reported it in GIT: https://github.com/salesagility/SuiteCRM/issues/9794

So have any good ideas or solutions?

Thanks