Custom query for filtering cases no longer works

Kixer — Mon, 13 May 2024 09:34:24 GMT

Hello,

I have a problem involving a custom query I wrote for filtering cases. For some reason the 'WHEN dr_prop_names.local_name = 'drDescription' AND dr_props.string_value ilike '%123%' THEN 1' part of the HAVING COUNT is returning nothing even though it should return something. For some reason dr_props join either does not work or it fails afterwards.

SELECT distinct subquery.id, subquery.uuid, case_props.string_value
FROM (
         SELECT distinct case_node.id, case_node.uuid, case_node.acl_id
         FROM alf_node parent
                  JOIN alf_store s ON parent.store_id = s.id
                  JOIN alf_child_assoc assoc ON parent.id = assoc.parent_node_id
                  JOIN alf_node case_node ON assoc.child_node_id = case_node.id
                  LEFT JOIN alf_node_properties case_props ON case_node.id = case_props.node_id
                  LEFT JOIN alf_qname case_prop_names ON case_props.qname_id = case_prop_names.id
                  LEFT JOIN alf_node dr_props_node ON case_props.string_value = dr_props_node.uuid
                  LEFT JOIN alf_node_properties dr_props ON dr_props_node.id = dr_props.node_id
                  LEFT JOIN alf_qname dr_prop_names ON dr_props.qname_id = dr_prop_names.id
         WHERE parent.uuid = '15a7b505-2061-4355-aef2-ea970bfba825'
           AND s.protocol = 'workspace'
           AND s.identifier = 'SpacesStore'
         GROUP BY case_node.id, case_node.uuid, case_node.acl_id, case_props.string_value
         HAVING COUNT(
                        CASE
                            WHEN case_prop_names.local_name = 'caseIsArchived' AND case_props.boolean_value = false
                                THEN 1
                            WHEN case_prop_names.local_name = 'caseIsRejected' AND case_props.boolean_value = false
                                THEN 1
                            WHEN dr_prop_names.local_name = 'drDescription' AND dr_props.string_value ilike '%123%'
                                THEN 1
                            END
                    ) = 3
     ) subquery
         JOIN alf_node_properties case_props ON subquery.id = case_props.node_id
         JOIN alf_qname case_prop_names ON case_props.qname_id = case_prop_names.id
    AND case_prop_names.local_name = 'caseDateReceived'
ORDER BY case_props.string_value DESC
LIMIT 10 OFFSET 0;

This query worked a year ago but since then I don't think anything has changed majorly.

If anyone has had a problem like this before I would appreaciate your help, thanks.

topic Custom query for filtering cases no longer works in Alfresco Forum

Custom query for filtering cases no longer works