05-13-2024 05:34 AM
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.
Explore our Alfresco products with the links below. Use labels to filter content by product module.