search

JMAConsulting / biz.jmaconsulting.activitytypeacl

This extension provides the ability to restrict activities on CiviCRM by roles with permissions to various activity types.
GNU Affero General Public License v3.0
4 stars 3 forks source link

Non-admins cannot export activities (activity_type_id in where clause is ambiguous) #19

Closed andyburnsco closed 4 years ago

andyburnsco commented 4 years ago

For non-admins, this is the error they get when going to Search > Find Activities > running search > actions dropdown > select Export Activities > error occurs. I disabled this extension and was able to get the export screen.

`Database Error Code: Column 'activity_type_id' in where clause is ambiguous, 1052 Additional Details:

Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => handle )

[code] => -1
[message] => DB Error: unknown error
[mode] => 16
[debug_info] => SELECT ( civicrm_activity.id ), contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.sort_name as `sort_name`, contact_a.display_name as `display_name`, civicrm_activity.id as activity_id, civicrm_activity.activity_type_id, civicrm_activity.subject as activity_subject, civicrm_activity.activity_date_time as activity_date_time, 1, civicrm_activity.duration as activity_duration, civicrm_activity.location as activity_location, civicrm_activity.details as activity_details, civicrm_activity.source_record_id as source_record_id, civicrm_activity.is_test as activity_is_test, civicrm_activity.campaign_id as activity_campaign_id, civicrm_activity.engagement_level as activity_engagement_level, source_contact.sort_name as source_contact, activity_priority.label as activity_priority,
  civicrm_activity.priority_id as priority_id, civicrm_activity.status_id as `status_id`, civicrm_activity.engagement_level as `engagement_level`  FROM civicrm_contact contact_a   LEFT JOIN civicrm_activity_contact
                  ON ( civicrm_activity_contact.contact_id = contact_a.id )  LEFT JOIN civicrm_activity
                  ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
                  AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 ) INNER JOIN civicrm_contact
                  ON ( civicrm_activity_contact.contact_id = civicrm_contact.id and civicrm_contact.is_deleted != 1 )  LEFT JOIN civicrm_option_group option_group_activity_priority ON (option_group_activity_priority.name = 'priority') LEFT JOIN civicrm_option_value activity_priority ON (civicrm_activity.priority_id = activity_priority.value
                          AND option_group_activity_priority.id = activity_priority.option_group_id )  LEFT JOIN civicrm_activity AS parent_id ON civicrm_activity.id = parent_id.parent_id  LEFT JOIN civicrm_activity_contact source_activity
        ON (source_activity.activity_id = civicrm_activity_contact.activity_id
          AND source_activity.record_type_id = 2)
      LEFT JOIN civicrm_contact source_contact ON (source_activity.contact_id = source_contact.id)  WHERE  ( civicrm_activity.activity_type_id IN ("93") AND civicrm_activity.activity_date_time BETWEEN '20200808000000' AND '20201006235959' AND civicrm_activity.status_id IN ("2") AND civicrm_activity.is_test = 0 )  AND  (  ( civicrm_activity.activity_type_id IN (76,77,2,3,1,4,5,6,7,8,12,17,19,34,40,35,36,37,38,39,44,22,46,47,48,51,58,54,52,78,80,81,84,85,93,94,96,97,98,100) )  )  AND (contact_a.is_deleted = 0) AND (civicrm_activity.activity_type_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 12, 17, 19, 22, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 54, 58, 76, 77, 78, 80, 81, 82, 84, 85, 86, 87, 93, 94, 95, 96, 97, 98, 99, 100, 101) AND ( activity_type_id NOT IN ( 9,13,14,15,16,18,20,21,23,24,25,26,27,28,29,30,31,32,33,41,42,43,45,49,50,53,82,86,87,95,99,101 )  ) AND civicrm_activity.`id` IN (SELECT `id` FROM `civicrm_activity` WHERE id IN (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id IN (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1))))   GROUP BY civicrm_activity.id  ORDER BY `contact_a`.`sort_name` ASC, `contact_a`.`id`  [nativecode=1052 ** Column 'activity_type_id' in where clause is ambiguous]
[type] => DB_Error
[user_info] => SELECT ( civicrm_activity.id ), contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.sort_name as `sort_name`, contact_a.display_name as `display_name`, civicrm_activity.id as activity_id, civicrm_activity.activity_type_id, civicrm_activity.subject as activity_subject, civicrm_activity.activity_date_time as activity_date_time, 1, civicrm_activity.duration as activity_duration, civicrm_activity.location as activity_location, civicrm_activity.details as activity_details, civicrm_activity.source_record_id as source_record_id, civicrm_activity.is_test as activity_is_test, civicrm_activity.campaign_id as activity_campaign_id, civicrm_activity.engagement_level as activity_engagement_level, source_contact.sort_name as source_contact, activity_priority.label as activity_priority,
  civicrm_activity.priority_id as priority_id, civicrm_activity.status_id as `status_id`, civicrm_activity.engagement_level as `engagement_level`  FROM civicrm_contact contact_a   LEFT JOIN civicrm_activity_contact
                  ON ( civicrm_activity_contact.contact_id = contact_a.id )  LEFT JOIN civicrm_activity
                  ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
                  AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 ) INNER JOIN civicrm_contact
                  ON ( civicrm_activity_contact.contact_id = civicrm_contact.id and civicrm_contact.is_deleted != 1 )  LEFT JOIN civicrm_option_group option_group_activity_priority ON (option_group_activity_priority.name = 'priority') LEFT JOIN civicrm_option_value activity_priority ON (civicrm_activity.priority_id = activity_priority.value
                          AND option_group_activity_priority.id = activity_priority.option_group_id )  LEFT JOIN civicrm_activity AS parent_id ON civicrm_activity.id = parent_id.parent_id  LEFT JOIN civicrm_activity_contact source_activity
        ON (source_activity.activity_id = civicrm_activity_contact.activity_id
          AND source_activity.record_type_id = 2)
      LEFT JOIN civicrm_contact source_contact ON (source_activity.contact_id = source_contact.id)  WHERE  ( civicrm_activity.activity_type_id IN ("93") AND civicrm_activity.activity_date_time BETWEEN '20200808000000' AND '20201006235959' AND civicrm_activity.status_id IN ("2") AND civicrm_activity.is_test = 0 )  AND  (  ( civicrm_activity.activity_type_id IN (76,77,2,3,1,4,5,6,7,8,12,17,19,34,40,35,36,37,38,39,44,22,46,47,48,51,58,54,52,78,80,81,84,85,93,94,96,97,98,100) )  )  AND (contact_a.is_deleted = 0) AND (civicrm_activity.activity_type_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 12, 17, 19, 22, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 54, 58, 76, 77, 78, 80, 81, 82, 84, 85, 86, 87, 93, 94, 95, 96, 97, 98, 99, 100, 101) AND ( activity_type_id NOT IN ( 9,13,14,15,16,18,20,21,23,24,25,26,27,28,29,30,31,32,33,41,42,43,45,49,50,53,82,86,87,95,99,101 )  ) AND civicrm_activity.`id` IN (SELECT `id` FROM `civicrm_activity` WHERE id IN (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id IN (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1))))   GROUP BY civicrm_activity.id  ORDER BY `contact_a`.`sort_name` ASC, `contact_a`.`id`  [nativecode=1052 ** Column 'activity_type_id' in where clause is ambiguous]
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT ( civicrm_activity.id ), contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.sort_name as `sort_name`, contact_a.display_name as `display_name`, civicrm_activity.id as activity_id, civicrm_activity.activity_type_id, civicrm_activity.subject as activity_subject, civicrm_activity.activity_date_time as activity_date_time, 1, civicrm_activity.duration as activity_duration, civicrm_activity.location as activity_location, civicrm_activity.details as activity_details, civicrm_activity.source_record_id as source_record_id, civicrm_activity.is_test as activity_is_test, civicrm_activity.campaign_id as activity_campaign_id, civicrm_activity.engagement_level as activity_engagement_level, source_contact.sort_name as source_contact, activity_priority.label as activity_priority,
  civicrm_activity.priority_id as priority_id, civicrm_activity.status_id as `status_id`, civicrm_activity.engagement_level as `engagement_level`  FROM civicrm_contact contact_a   LEFT JOIN civicrm_activity_contact
                  ON ( civicrm_activity_contact.contact_id = contact_a.id )  LEFT JOIN civicrm_activity
                  ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
                  AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 ) INNER JOIN civicrm_contact
                  ON ( civicrm_activity_contact.contact_id = civicrm_contact.id and civicrm_contact.is_deleted != 1 )  LEFT JOIN civicrm_option_group option_group_activity_priority ON (option_group_activity_priority.name = 'priority') LEFT JOIN civicrm_option_value activity_priority ON (civicrm_activity.priority_id = activity_priority.value
                          AND option_group_activity_priority.id = activity_priority.option_group_id )  LEFT JOIN civicrm_activity AS parent_id ON civicrm_activity.id = parent_id.parent_id  LEFT JOIN civicrm_activity_contact source_activity
        ON (source_activity.activity_id = civicrm_activity_contact.activity_id
          AND source_activity.record_type_id = 2)
      LEFT JOIN civicrm_contact source_contact ON (source_activity.contact_id = source_contact.id)  WHERE  ( civicrm_activity.activity_type_id IN ("93") AND civicrm_activity.activity_date_time BETWEEN '20200808000000' AND '20201006235959' AND civicrm_activity.status_id IN ("2") AND civicrm_activity.is_test = 0 )  AND  (  ( civicrm_activity.activity_type_id IN (76,77,2,3,1,4,5,6,7,8,12,17,19,34,40,35,36,37,38,39,44,22,46,47,48,51,58,54,52,78,80,81,84,85,93,94,96,97,98,100) )  )  AND (contact_a.is_deleted = 0) AND (civicrm_activity.activity_type_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 12, 17, 19, 22, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 54, 58, 76, 77, 78, 80, 81, 82, 84, 85, 86, 87, 93, 94, 95, 96, 97, 98, 99, 100, 101) AND ( activity_type_id NOT IN ( 9,13,14,15,16,18,20,21,23,24,25,26,27,28,29,30,31,32,33,41,42,43,45,49,50,53,82,86,87,95,99,101 )  ) AND civicrm_activity.`id` IN (SELECT `id` FROM `civicrm_activity` WHERE id IN (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id IN (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1))))   GROUP BY civicrm_activity.id  ORDER BY `contact_a`.`sort_name` ASC, `contact_a`.`id`  [nativecode=1052 ** Column 'activity_type_id' in where clause is ambiguous]"]

) `

andyburnsco commented 4 years ago

Also in advance search, if I search for activities and want to display as contacts, the export similarly fails for non-admins:

activity search error

seamuslee001 commented 4 years ago

@andyburnsco I have filed https://github.com/JMAConsulting/biz.jmaconsulting.activitytypeacl/pull/20 which I believe will fix the first issue you have identified. I don't believe that it will sort the Memcache problem tho. Not sure if that is related to this extension or is just a coincidence

andyburnsco commented 4 years ago

That did it :) Advance Search also worked. Thank you!