List of files not displayed (API case getfiles)

[tttp]

Hi,

Uploading files seems to work great but looking at the ajax calls, there is an error:

error_code	"unknown error"
sql	"SELECT DISTINCT caseact.case_id as case_id, caseact.activity_id as activity_id, f.id as id, act.activity_date_time\nFROM civicrm_case_activity caseact\nINNER JOIN civicrm_entity_file ef ON (ef.entity_table = \"civicrm_activity\" AND ef.entity_id = caseact.activity_id) \nLEFT JOIN civicrm_entity_tag et ON (et.entity_table = \"civicrm_activity\" AND et.entity_id = caseact.activity_id) \nINNER JOIN civicrm_file f ON ef.file_id = f.id\nINNER JOIN civicrm_activity act ON ((caseact.activity_id = act.id OR caseact.activity_id = act.original_id) AND act.is_current_revision=1)\nWHERE (caseact.case_id = 22) AND (act.subject LIKE \"%%\" OR act.details LIKE \"%%\" OR f.description LIKE \"%%\" OR f.uri LIKE \"%%\" OR f.uri LIKE \"%\")\nORDER BY act.activity_date_time DESC, act.id DESC, f.id DESC\n [nativecode=3065 ** Expression #2 of ORDER BY clause is not in SELECT list, references column 'crm.act.id' which is not in SELECT list; this is incompatible with DISTINCT]"
tip	"add debug=1 to your API call to have more info about the error"
is_error	1
error_message	"DB Error: unknown error"
debug_information	"SELECT DISTINCT caseact.case_id as case_id, caseact.activity_id as activity_id, f.id as id, act.activity_date_time\nFROM civicrm_case_activity caseact\nINNER JOIN civicrm_entity_file ef ON (ef.entity_table = \"civicrm_activity\" AND ef.entity_id = caseact.activity_id) \nLEFT JOIN civicrm_entity_tag et ON (et.entity_table = \"civicrm_activity\" AND et.entity_id = caseact.activity_id) \nINNER JOIN civicrm_file f ON ef.file_id = f.id\nINNER JOIN civicrm_activity act ON ((caseact.activity_id = act.id OR caseact.activity_id = act.original_id) AND act.is_current_revision=1)\nWHERE (caseact.case_id = 22) AND (act.subject LIKE \"%%\" OR act.details LIKE \"%%\" OR f.description LIKE \"%%\" OR f.uri LIKE \"%%\" OR f.uri LIKE \"%\")\nORDER BY act.activity_date_time DESC, act.id DESC, f.id DESC\n [nativecode=3065 ** Expression #2 of ORDER BY clause is not in SELECT list, references column 'crm.act.id' which is not in SELECT list; this is incompatible with DISTINCT]"

The fix
->select('caseact.case_id as case_id, act.id as activity_id, f.id as id, act.activity_date_time')

seems to work

git diff api/v3/Case/Getfiles.php
diff --git a/api/v3/Case/Getfiles.php b/api/v3/Case/Getfiles.php
index 4be9f9b3..5e6d147a 100644
--- a/api/v3/Case/Getfiles.php
+++ b/api/v3/Case/Getfiles.php
@@ -148,7 +148,7 @@ function _civicrm_api3_case_getfiles_select(array $params) {
     ->join('ef', 'INNER JOIN civicrm_entity_file ef ON (ef.entity_table = "civicrm_activity" AND ef.entity_id = caseact.activity_id) ')
     ->join('et', 'LEFT JOIN civicrm_entity_tag et ON (et.entity_table = "civicrm_activity" AND et.entity_id = caseact.activity_id) ')
     ->join('f', 'INNER JOIN civicrm_file f ON ef.file_id = f.id')
-    ->select('caseact.case_id as case_id, caseact.activity_id as activity_id, f.id as id, act.activity_date_time')
+    ->select('caseact.case_id as case_id, act.id as activity_id, f.id as id, act.activity_date_time')
     ->distinct();
 
   if (isset($params['case_id'])) {

[fran-compucorp]

Thanks @tttp - I'll have a look into this and get back to you.

[tttp]

if it helps, can do the PR dance, but it's a one line change so, out of laziness... ;P

…

On Fri, 21 Aug 2020 at 08:54, fran-compucorp ***@***.***> wrote: Thanks @tttp <https://github.com/tttp> - I'll have a look into this and get back to you. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#563 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAA7LKXKGV4UADCRXDWXH2LSBYKYZANCNFSM4QGDXIPA> .