Suitecrm: AOR_Reports - Assigning report to user other than SuperAdmin causes error in resulting SQL query

[jellydonuts]

If you assign a report (or even create a report) as a user other than the super admin, the resulting SQL query created is faulty.

_AOR_Reports.php_: function build_group_report($offset = -1, $links = true, $extra = array())

_Error if run at mysql cmdline_: ERROR 1054 (42S22): Unknown column 'accounts.assigned_user_id' in 'on clause'

SELECT `tasks`.status AS 'Status', `tasks:accounts`.id AS 'tasks:accounts_id' FROM tasks LEFT JOIN `tasks_cstm` `tasks_cstm` ON `tasks`.id = `tasks_cstm`.id_c LEFT JOIN accounts `tasks:accounts` ON `tasks`.parent_id=`tasks:accounts`.id AND `tasks:accounts`.deleted=0
 AND `tasks`.parent_type = 'Accounts'
 AND ( accounts.assigned_user_id ='7179215b-2be4-9405-9ad4-56bb8d80ae3a'  or  EXISTS (SELECT  1
                  FROM    securitygroups secg
                          INNER JOIN securitygroups_users secu
                            ON secg.id = secu.securitygroup_id
                               AND secu.deleted = 0
                               AND secu.user_id = '7179215b-2be4-9405-9ad4-56bb8d80ae3a'
                          INNER JOIN securitygroups_records secr
                            ON secg.id = secr.securitygroup_id
                               AND secr.deleted = 0
                               AND secr.module = 'Accounts'
                       WHERE   secr.record_id = accounts.id
                               AND secg.deleted = 0) )  WHERE ( `tasks`.status IS NOT NULL AND  ( ( (`tasks`.status = 'Not Started' OR `tasks`.status = 'In Progress' OR `tasks`.status = 'Completed' OR `tasks`.status = 'Pending Input' OR `tasks`.status = 'Deferred') AND `tasks`.assigned_user_id LIKE CONCAT('%', '' ,'%') ) AND ( `tasks_cstm`.task_started_c >= "2016-02-01 00:00:00" AND `tasks_cstm`.task_started_c < "2016-03-01 00:00:00" ) AND ( `tasks:accounts`.billing_address_city LIKE CONCAT('%', '' ,'%') AND `tasks:accounts`.billing_address_state LIKE CONCAT('%', '' ,'%') ) ) AND  tasks.deleted = 0  AND ( tasks.assigned_user_id ='7179215b-2be4-9405-9ad4-56bb8d80ae3a'  or  EXISTS (SELECT  1
                  FROM    securitygroups secg
                          INNER JOIN securitygroups_users secu
                            ON secg.id = secu.securitygroup_id
                               AND secu.deleted = 0
                               AND secu.user_id = '7179215b-2be4-9405-9ad4-56bb8d80ae3a'
                          INNER JOIN securitygroups_records secr
                            ON secg.id = secr.securitygroup_id
                               AND secr.deleted = 0
                               AND secr.module = 'Tasks'
                       WHERE   secr.record_id = tasks.id
                               AND secg.deleted = 0) )  ) group by Status

[jellydonuts]

I believe the resulting query should be as follows, though I haven't looked at JOINS in SQL in years so i could be wrong...

_you join the task table with the accounts table, as tasks:accounts but then in the where clauses, you never use task:accounts again, you simply use accounts which hasn't been included in the join_

`SELECT `tasks`.status AS 'Status', `tasks:accounts`.id AS 'tasks:accounts_id' FROM tasks LEFT JOIN `tasks_cstm` `tasks_cstm` ON `tasks`.id = `tasks_cstm`.id_c LEFT JOIN accounts `tasks:accounts` ON `tasks`.parent_id=`tasks:accounts`.id AND `tasks:accounts`.deleted=0
 AND `tasks`.parent_type = 'Accounts'
 AND ( `tasks:accounts`.assigned_user_id ='7179215b-2be4-9405-9ad4-56bb8d80ae3a'  or  EXISTS (SELECT  1
                  FROM    securitygroups secg
                          INNER JOIN securitygroups_users secu
                            ON secg.id = secu.securitygroup_id
                               AND secu.deleted = 0
                               AND secu.user_id = '7179215b-2be4-9405-9ad4-56bb8d80ae3a'
                          INNER JOIN securitygroups_records secr
                            ON secg.id = secr.securitygroup_id
                               AND secr.deleted = 0
                               AND secr.module = 'Accounts'
                       WHERE   secr.record_id = `tasks:accounts`.id
                               AND secg.deleted = 0) )  WHERE ( `tasks`.status IS NOT NULL AND  ( ( (`tasks`.status = 'Not Started' OR `tasks`.status = 'In Progress' OR `tasks`.status = 'Completed' OR `tasks`.status = 'Pending Input' OR `tasks`.status = 'Deferred') AND `tasks`.assigned_user_id LIKE CONCAT('%', '' ,'%') ) AND ( `tasks_cstm`.task_started_c >= "2016-02-01 00:00:00" AND `tasks_cstm`.task_started_c < "2016-03-01 00:00:00" ) AND ( `tasks:accounts`.billing_address_city LIKE CONCAT('%', '' ,'%') AND `tasks:accounts`.billing_address_state LIKE CONCAT('%', '' ,'%') ) ) AND  tasks.deleted = 0  AND ( tasks.assigned_user_id ='7179215b-2be4-9405-9ad4-56bb8d80ae3a'  or  EXISTS (SELECT  1
                  FROM    securitygroups secg
                          INNER JOIN securitygroups_users secu
                            ON secg.id = secu.securitygroup_id
                               AND secu.deleted = 0
                               AND secu.user_id = '7179215b-2be4-9405-9ad4-56bb8d80ae3a'
                          INNER JOIN securitygroups_records secr
                            ON secg.id = secr.securitygroup_id
                               AND secr.deleted = 0
                               AND secr.module = 'Tasks'
                       WHERE   secr.record_id = tasks.id
                               AND secg.deleted = 0) )  ) group by Status`

[jellydonuts]

thanks for the fix, i've altered my own version and it looks fixed.

[emcrobert]

No probs. Glad to be of assistance