Dashboard Query failed to detect existing record for contact
Overview
Steps to Re-create issue
Create New role with limit permission
view all activities
access CiviCRM backend and API
add contact notes
access CiviMember
access CiviMail
access CiviReport
access Report Criteria
Create New user and assign new role.
Now login, and click on CiviCRM menu. Dashboard screen showing DB Error message.
We are using API 4 to fetch the dashboard contact details.
Since role have limited access it uses ACL clause.
Here is full Query.
SELECT
`a`.`id` AS `id`,
`a`.`domain_id` AS `domain_id`,
`a`.`name` AS `name`,
`a`.`label` AS `label`,
`a`.`url` AS `url`,
`a`.`permission` AS `permission`,
`a`.`permission_operator` AS `permission_operator`,
`a`.`fullscreen_url` AS `fullscreen_url`,
`a`.`is_active` AS `is_active`,
`a`.`is_reserved` AS `is_reserved`,
`a`.`cache_minutes` AS `cache_minutes`,
`a`.`directive` AS `directive`,
`dashboard_contact`.`id` AS `dashboard_contact.id`,
`dashboard_contact`.`dashboard_id` AS `dashboard_contact.dashboard_id`,
`dashboard_contact`.`contact_id` AS `dashboard_contact.contact_id`,
`dashboard_contact`.`column_no` AS `dashboard_contact.column_no`,
`dashboard_contact`.`is_active` AS `dashboard_contact.is_active`,
`dashboard_contact`.`weight` AS `dashboard_contact.weight`
FROM civicrm_dashboard a
LEFT JOIN `civicrm_dashboard_contact` `dashboard_contact`
ON (
`dashboard_contact`.`contact_id` IS NULL
OR (`dashboard_contact`.`contact_id` IN (
SELECT contact_id FROM civicrm_acl_contact_cache WHERE user_id = 210
)
AND `dashboard_contact`.`contact_id` IN -- AND has be OR
(
SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1
)
)
)
AND `dashboard_contact`.`dashboard_id` = `a`.`id` AND `dashboard_contact`.`contact_id` = 210
WHERE (`a`.`domain_id` = "1")
and a.name = 'getting-started' -- added this for testing single record
ORDER BY `dashboard_contact`.`weight` ASC
ACL Query is generated from here : https://github.com/civicrm/civicrm-core/blob/f7dc44067e309188431deb793e74cb18bdd97827/CRM/Core/DAO.php#L3003
(`dashboard_contact`.`contact_id` IS NULL
OR
(`dashboard_contact`.`contact_id` IN (SELECT contact_id FROM civicrm_acl_contact_cache WHERE user_id = 210)
AND
`dashboard_contact`.`contact_id` IN (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1)))
IF we changes
index b80bc54575..d7d3903e28 100644
--- a/CRM/Core/DAO.php
+++ b/CRM/Core/DAO.php
@@ -3000,7 +3000,7 @@ SELECT contact_id
foreach ((array) $bao->addSelectWhereClause() as $field => $vals) {
$clauses[$field] = NULL;
if ($vals) {
- $clauses[$field] = "(`$tableAlias`.`$field` IS NULL OR (`$tableAlias`.`$field` " . implode(" AND `$tableAlias`.`$field` ", (array) $vals) . '))';
+ $clauses[$field] = "(`$tableAlias`.`$field` IS NULL OR (`$tableAlias`.`$field` " . implode(" OR `$tableAlias`.`$field` ", (array) $vals) . '))';
}
}
ACL Query changed to
(`dashboard_contact`.`contact_id` IS NULL
OR
(`dashboard_contact`.`contact_id` IN (SELECT contact_id FROM civicrm_acl_contact_cache WHERE user_id = 210)
OR
`dashboard_contact`.`contact_id` IN (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1)))
Dashboard function is able to detect dashlet is present of contact https://github.com/civicrm/civicrm-core/blob/f7dc44067e309188431deb793e74cb18bdd97827/CRM/Core/BAO/Dashboard.php#L62 And there no insert statement
Otherwise We get sql query to insert new record and get DB Error
INSERT INTO `civicrm_dashboard_contact` (`dashboard_id` , `contact_id` , `column_no` , `is_active` ) VALUES ( 1 , 210 , 1 , 1 )
[nativecode=1062 ** Duplicate entry '1-210' for key 'index_dashboard_id_contact_id']"
Tested on 5.36.alpha1