Chart Report not working with Security Group Filter User List #5347

New issue

Open

opened 2026-02-20 16:59:59 -05:00 by deekerman · 1 comment

deekerman commented

2026-02-20 16:59:59 -05:00

Owner

Originally created by @smtaha01 on GitHub (Oct 20, 2025).

Issue

When Security Group setting Filter User List is checked, then a report, which has related User field, does not work due to sql query error.

Possible Fix

data/SugarBean.php

in public function buildAccessWhere

Current Code:

if ($view === 'list' && $this->module_dir === 'Users' && !is_admin($user)
            && isset($sugar_config['securitysuite_filter_user_list'])
            && $sugar_config['securitysuite_filter_user_list']
        ) {
            $groupWhere = SecurityGroup::getGroupUsersWhere($user->id);
            $conditions['group'] = $groupWhere;
        }

Should be:

if ($view === 'list' && $this->module_dir === 'Users' && !is_admin($user)
            && isset($sugar_config['securitysuite_filter_user_list'])
            && $sugar_config['securitysuite_filter_user_list']
        ) {
            $groupWhere = SecurityGroup::getGroupUsersWhere($user->id, $this->table_name);
            $conditions['group'] = $groupWhere;
        }

modules/SecurityGroups/SecurityGroup.php
in function: getGroupUsersWhere
Current Code:

public static function getGroupUsersWhere($user_id)
    {
        $db = DBManagerFactory::getInstance();
        $quotedUserId = $db->quote($user_id);
        return " users.id in (
            select sec.user_id from securitygroups_users sec
            inner join securitygroups_users secu on sec.securitygroup_id = secu.securitygroup_id and secu.deleted = 0
                and secu.user_id = '$quotedUserId'
            where sec.deleted = 0
        )";
    }

Should be:

public static function getGroupUsersWhere($user_id, $alias = 'users')
    {
        $db = DBManagerFactory::getInstance();
        $quotedUserId = $db->quote($user_id);
        return " $alias.id in (
            select sec.user_id from securitygroups_users sec
            inner join securitygroups_users secu on sec.securitygroup_id = secu.securitygroup_id and secu.deleted = 0
                and secu.user_id = '$quotedUserId'
            where sec.deleted = 0
        )";
    }

Steps to Reproduce the Issue

1. Create a Report of Module `Calls`, with field Assigned User and Duration Minutes (use Funtion Sum)
2. Now Add Bar Chart with Assigned User on X axis and Duration Minutes on Y axis
3. Save and Open this report with Regular user

Current Behaviour: Chart and Data does not show up
Expected Behavious: Chart and Data should appear just like it appears in Admin User

Context

[FATAL]  Query Failed: SELECT `calls:assigned_user_link`.first_name AS 'First_Name0', SUM(`calls`.total_duration) AS 'Total_Minutes1' FROM `calls` LEFT JOIN users `calls:assigned_user_link` ON `calls`.assigned_user_id=`calls:assigned_user_link`.id AND `calls:assigned_user_link`.deleted=0

 AND  users.id in (
            select sec.user_id from securitygroups_users sec
            inner join securitygroups_users secu on sec.securitygroup_id = secu.securitygroup_id and secu.deleted = 0
                and secu.user_id = '********-****-****-****-********'
            where sec.deleted = 0
        ) WHERE ( DATE(`calls`.date_entered) = Curdate() AND `calls`.status = 'Held' ) AND  calls.deleted = 0  GROUP BY `calls:assigned_user_link`.first_name: MySQL error 1054: Unknown column 'users.id' in 'IN/ALL/ANY subquery'

The Issue here is in modules\SecurityGroups\SecurityGroup.php : public static function getGroupUsersWhere
where it:

return " users.id in (
It should use Alias of the table user which is being used in the Reports

Version

7.14.5

What browser are you currently using?

Chrome

Browser Version

No response

Environment Information

Php 8.2, MySQL 8.0

Operating System and Version

Ubuntu 20

Originally created by @smtaha01 on GitHub (Oct 20, 2025). ### Issue When Security Group setting `Filter User List` is checked, then a report, which has related User field, does not work due to sql query error. ### Possible Fix `data/SugarBean.php` in `public function buildAccessWhere` **Current Code**: ```` if ($view === 'list' && $this->module_dir === 'Users' && !is_admin($user) && isset($sugar_config['securitysuite_filter_user_list']) && $sugar_config['securitysuite_filter_user_list'] ) { $groupWhere = SecurityGroup::getGroupUsersWhere($user->id); $conditions['group'] = $groupWhere; } ```` **Should be**: ```` if ($view === 'list' && $this->module_dir === 'Users' && !is_admin($user) && isset($sugar_config['securitysuite_filter_user_list']) && $sugar_config['securitysuite_filter_user_list'] ) { $groupWhere = SecurityGroup::getGroupUsersWhere($user->id, $this->table_name); $conditions['group'] = $groupWhere; } ```` `modules/SecurityGroups/SecurityGroup.php` in function: `getGroupUsersWhere` **Current Code**: ```` public static function getGroupUsersWhere($user_id) { $db = DBManagerFactory::getInstance(); $quotedUserId = $db->quote($user_id); return " users.id in ( select sec.user_id from securitygroups_users sec inner join securitygroups_users secu on sec.securitygroup_id = secu.securitygroup_id and secu.deleted = 0 and secu.user_id = '$quotedUserId' where sec.deleted = 0 )"; } ```` **Should be**: ```` public static function getGroupUsersWhere($user_id, $alias = 'users') { $db = DBManagerFactory::getInstance(); $quotedUserId = $db->quote($user_id); return " $alias.id in ( select sec.user_id from securitygroups_users sec inner join securitygroups_users secu on sec.securitygroup_id = secu.securitygroup_id and secu.deleted = 0 and secu.user_id = '$quotedUserId' where sec.deleted = 0 )"; } ```` ### Steps to Reproduce the Issue ```bash 1. Create a Report of Module `Calls`, with field Assigned User and Duration Minutes (use Funtion Sum) 2. Now Add Bar Chart with Assigned User on X axis and Duration Minutes on Y axis 3. Save and Open this report with Regular user Current Behaviour: Chart and Data does not show up Expected Behavious: Chart and Data should appear just like it appears in Admin User ``` ### Context ``` [FATAL] Query Failed: SELECT `calls:assigned_user_link`.first_name AS 'First_Name0', SUM(`calls`.total_duration) AS 'Total_Minutes1' FROM `calls` LEFT JOIN users `calls:assigned_user_link` ON `calls`.assigned_user_id=`calls:assigned_user_link`.id AND `calls:assigned_user_link`.deleted=0 AND users.id in ( select sec.user_id from securitygroups_users sec inner join securitygroups_users secu on sec.securitygroup_id = secu.securitygroup_id and secu.deleted = 0 and secu.user_id = '********-****-****-****-********' where sec.deleted = 0 ) WHERE ( DATE(`calls`.date_entered) = Curdate() AND `calls`.status = 'Held' ) AND calls.deleted = 0 GROUP BY `calls:assigned_user_link`.first_name: MySQL error 1054: Unknown column 'users.id' in 'IN/ALL/ANY subquery' ``` The Issue here is in `modules\SecurityGroups\SecurityGroup.php` : `public static function getGroupUsersWhere` where it: ` return " users.id in ( ` It should use Alias of the table user which is being used in the Reports ### Version 7.14.5 ### What browser are you currently using? Chrome ### Browser Version _No response_ ### Environment Information Php 8.2, MySQL 8.0 ### Operating System and Version Ubuntu 20